12,076,727 members (44,702 online)
Rate this:
See more:
My Table
```Month	Year	Dealer	Dealer_Code	City	 Active	Points
9	2012	Jaswant Motors	10000	Jalandhar  1	500
9	2012	Speedways	10001	Amritsar	   1	1300
9	2012	Jaswant Motors	10000	Jalandhar  1	279
9	2012	Speedways	10001	Amritsar	   1	88```

Desired Output

```Month	Dealer	                             Service_Points	manth	year
Sep-12	Jaswant Motors - 10000 - Jalandhar 	779	          9	2012
Sep-12	Speedways - 10001 - Amritsar	        1388	          9	2012
Sep-12	Total	                                  2167	         [123]	[1234]
Total	Jaswant Motors - 10000 - Jalandhar	         779	         [234]	[2345]
Total	Speedways - 10001 - Amritsar	        1388	         [234]	[2345]
Total	Total	                                  2167	         [234]	[345]```

Note:-Values in Square Brackets([]) are Imaginary value
Posted 3-Dec-12 19:50pm
Edited 3-Dec-12 20:01pm
__TR__29K
v2
__TR__ 4-Dec-12 1:09am

Can you please explain what do you mean by Imaginary values ? What data should be populated in the last 2 columns of your result?

Rate this:

Rate this:

## Solution 1

Try below sql

`select convert( varchar(3) , DATENAME(MM,DATEADD(mm,Month,-1)) ) + '-' + right (convert(varchar,Year ),2) as Month ,Dealer ,Service_Points, manth, year from TableName`
sukhen dass 4-Dec-12 3:19am

Thanks Kasim...but your query will not give the desired output because there is no group by included..please,refer the desired output given
Rate this:

## Solution 2

You are going to have to break this up into multiple queries.

First get the totals into a temp table matching your output, all fields will have to be varchar as you are mixing data types, a really crappy idea.

Then you select your detail records and use a `UNION `to the temp table, you may want to add an ID column to insure you can sort the data correctly.

This is a particularly silly query as the totalling should be done in your consuming application.
Rate this:

## Solution 3

`Select left(DateName( month , DateAdd(MONTH , [Month]* , 0 ) - 1 ),3)+ '-' + substring(CAST([year]* as CHAR(4)), 3, 2) AS Month,Dealer + '-' + Dealer_Code + '-' + City as Dealer,Points as Service point ,manth ,Month from MyTable`

[Month]* and [year]* is the column name of the table MyTable

Keep smiling Happy coding

Top Experts
Last 24hrsThis month
 Jochen Arndt 350 Richard MacCutchan 340 Sergey Alexandrovich Kryukov 319 OriginalGriff 275 F-ES Sitecore 220
 OriginalGriff 2,855 Dave Kreskowiak 2,836 Richard MacCutchan 2,174 CPallini 1,632 Sergey Alexandrovich Kryukov 1,416