I have problem in getting the desired output with the SQL query.
My sql data is as follows:
TOTAL Charge PAYMNET A B C D E MonthYear
------- ----------- ----------- --------- -------- ---------- ------- ------- ----------
661 157832.24 82967.80 700.00 10.70 58329.33 0.00 0.00 Oct-2013
612 95030.52 17824.28 850.00 66.10 53971.41 0.00 0.00 Nov-2013
584 90256.35 16732.91 700.00 66.10 52219.87 0.00 0.00 Dec-2013
511 72217.32 12336.12 285.00 53.17 42951.12 0.00 0.00 Jan-2014
I need the output as follows,
Data Jan-2013 Feb-2013 Mar-2013
TOTALCOUNT 761 647 671
Charge 126888 119995 151737.5
Payment 25705.4 26235.47 28704.41
A 1089.08 1020 745
B 2100.4 1947.25 1868.22
C 94246.55 84202.15 115673.7
D 0 0 0
E 0 0 0
I have seen the examples of pivot and unpivot, in pivot I don't get the column headers as row data, and in unpivot I didn't found an example where I can transpose multiple columns. I have another option to get this result in the code. But I want to know is it possible to get this kind of result in sql?
Edit
The result will give only for 3 or 4 months, not more than that.
Update : The first sample data is the actual data which I will get as a result of multiple joins and grouping on multiple tables, which I will store into a temp table. I tried to get the required result by modifying the query which is not possible because of the table structure. I managed to get the result as in the first sample data, but this is not what the client want to see!!! So I need to process the temp table data which will be only 3 to 4 rows into required output. The query to get the first result is select * from temp. The processing needs to be done on temp table result.
Update-2
I have tried the following query
declare @cols varchar(max)
select @cols = STUFF((select ', ' + MonthYear
from #tmp for xml path('')),1,1,'')
declare @query varchar(max)
set @query =
'select ''TOTAL'' as Data,' +@cols+' from
(select MonthYear,TOTALCLAIMS from #tmp)st
pivot
(
MAX(TOTAL) for MonthYear in (' + @cols + ')
)pt;'
Which gave me the first row correctly!!! But I tried to use union as
set @query =
'select ''TOTAL'' as Data,' +@cols+' from
(select MonthYear,TOTALCLAIMS from #tmp)st
pivot
(
MAX(TOTAL) for MonthYear in (' + @cols + ')
)pt;
union
select ''CHARGES'' as Data,' +@cols+' from
(select MonthYear,TOTALCLAIMS from #tmp)st
pivot
(
MAX(CHARGES) for MonthYear in (' + @cols + ')
)pt;'
Which gives an error as incorrect syntax near union. Any one know how to union pivot results? Or is there any better way to do this?
Thank You.
I have tried this code. Please check and let me know if it works
I know that it doesnt look so good. Also not sure how it will be performance wise.
--Can have more columns like A,B,...
DECLARE @tbl TABLE
(
TOTAL INT,
CHARGE FLOAT,
PAYMENT FLOAT,
MONTHYEAR VARCHAR(50)
)
--Test data
INSERT INTO @tbl SELECT 661, 157832.24, 82967.80, 'Oct2013'
INSERT INTO @tbl SELECT 612, 95030.52, 17824.28, 'Nov2013'
INSERT INTO @tbl SELECT 584 ,90256.35, 16732.91, 'Dec2013'
--Can be a physical table
CREATE TABLE #FinalTbl
(
DATA VARCHAR(100)
)
--inserted hardcode records in data column. To add it dynamically you would need to loop through information_schema.columns
--SELECT *
--FROM information_schema.columns
--WHERE table_name = 'tbl_name'
INSERT INTO #FinalTbl
VALUES ('TOTAL')
INSERT INTO #FinalTbl
VALUES ('CHARGE')
INSERT INTO #FinalTbl
VALUES ('PAYMENT')
DECLARE @StartCount INT, @TotalCount INT, @Query VARCHAR(5000), @TOTAL INT,@CHARGE FLOAT,@PAYMENT FLOAT,@MONTHYEAR VARCHAR(50)
SELECT @TotalCount = COUNT(*) FROM @tbl;
SET @StartCount = 1;
WHILE(@StartCount <= @TotalCount)
BEGIN
SELECT @TOTAL = TOTAL,
@CHARGE = CHARGE,
@PAYMENT = PAYMENT,
@MONTHYEAR = MONTHYEAR
FROM
(SELECT ROW_NUMBER() over(ORDER BY MONTHYEAR) AS ROWNUM, * FROM @tbl) as tbl
WHERE ROWNUM = @StartCount
SELECT @Query = 'ALTER TABLE #FinalTbl ADD ' + @MONTHYEAR + ' VARCHAR(1000)'
EXEC (@Query)
SELECT @Query = 'UPDATE #FinalTbl SET ' + @MONTHYEAR + ' = ''' + CONVERT(VARCHAR(50), @TOTAL) + ''' WHERE DATA = ''TOTAL'''
EXEC (@Query)
SELECT @Query = 'UPDATE #FinalTbl SET ' + @MONTHYEAR + ' = ''' + CONVERT(VARCHAR(50), @CHARGE) + ''' WHERE DATA = ''CHARGE'''
EXEC (@Query)
SELECT @Query = 'UPDATE #FinalTbl SET ' + @MONTHYEAR + ' = ''' + CONVERT(VARCHAR(50), @PAYMENT) + ''' WHERE DATA = ''PAYMENT'''
EXEC (@Query)
SELECT @StartCount = @StartCount + 1
END
SELECT * FROM #FinalTbl
DROP TABLE #FinalTbl
Hope this helps