transpose rows to columns in sql

问题:

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.

回答1:

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

回答2:

I would imagine the reason you are only getting 3 or 4 months is because you don't have data for the missing months? If you want to display columns for missing months you will need to either:

1. Create a Table datatype with all the months you want to display and left join the remainder of the tables to it in your query. You could then use the PIVOT function as normal.

2. If you know how many columns up front i.e. one for each month in a particular year and it won't change, you can simply use CASE Statements (one for each month) to transpose the data without the PIVOT operator.

I can provide examples if needed.

回答3:

Select Month(Mdate)md,'A' AS Col,sum(A) as a from Product group by Month(MDate)
union all
Select Month(Mdate)md,'B',sum(b) as a from Product group by Month(MDate)
union all
Select Month(Mdate)md,'C',sum(c) as a from Product group by Month(MDate)
union all
Select Month(Mdate)md,'D',Count(A) as a from Product group by Month(MDate)

Try Pivot with the above query you may to get required result....