Need help of pivot query in sql

Question

I am using SQL Server 2014. I have a table like this:

  MONTH      BILL AMOUNT       PAID AMOUNT
  Jan        2937.00           2940.00
  Dec        2938.00           2940.00
  Nov           0.00              0.00
  Oct        2940.00           2940.00
  Sep        2935.00           2940.00
  Aug        2936.00           2940.00

I want output like this:

Bill Detail for previous 6 month

  Month            Jan        Dec    Nov     Oct     Sep       Aug
    Bill Amount   2937.00    2938.00  0    2940.00 2935.00    2936.00
    Paid Amount   2940.00    2940.00  0    2940.00 2940.00    2940.00

Can anyone suggest a solution?

Possible duplicate of [Simple way to transpose columns and rows in Sql?](http://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql) — Andrea, Mar 08 '17 at 08:38

DJs · Answer 1 · 2017-03-08T09:28:53.420

I tried using SQL Server 2008. Specified Static column names for the months.

select name as [MONTH], [Jan], [Dec], [Nov], [Oct], [Sep], [Aug]
from
(
 select [MONTH], name, value
 from testtable
 unpivot
 (
  value for name in (BILLAMOUNT, PAIDAMOUNT)
 ) unpiv
) src
 pivot
 (
 sum(value)
 for [MONTH] in ([Jan], [Dec], [Nov], [Oct], [Sep], [Aug])
) piv

When dynamic columns for month is required :-

DECLARE @colsUnpivot AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @colsPivot as  NVARCHAR(MAX)

select @colsUnpivot = stuff((select ','+quotename(C.name)
         from sys.columns as C
         where C.object_id = object_id('test2') and
               C.name <> 'MONTH'
         for xml path('')), 1, 1, '')

select @colsPivot = STUFF((SELECT  ',' 
                      + quotename([MONTH])
                    from testtable t
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query 
  = 'select name as [MONTH], '+@colsPivot+'
      from
      (
        select [MONTH], name, value
        from testtable
        unpivot
        (
         value for name in ('+@colsUnpivot+')
        ) unpiv
      ) src
      pivot
      (
        sum(value)
        for [MONTH] in ('+@colsPivot+')
      ) piv'

exec(@query)

Momth is dynamic. Not nessasary that month is Jan, Dec, Nov, Oct, Sep,Aug. This is last 6 month. If some one take this report in July then month come like : Jun, May, Apr, Mar, Feb, Jan — Sanjib Mukherjee, Mar 08 '17 at 09:06
@SanjibMukherjee I have edited the answer for getting the dynamic months as columns. I have referred the link [Simple way to transpose columns](http://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql). You can refer the link for furthur assisstance. — DJs, Mar 08 '17 at 09:32

score 0 · Answer 2 · answered Mar 08 '17 at 09:20

You can try this,

    Create table #billing
    (
    [MONTH] varchar(15),
    [BILL_AMOUNT] Decimal(8,2),
    [PAID_AMOUNT] Decimal(8,2)
    )

    Insert Into #billing
    Values('Jan', 2937.00, 2940.00),
          ('Dec', 2938.00, 2940.00),
          ('Nov',  0.00  , 0.00 ),
          ('Oct', 2940.00, 2940.00),
          ('Sep', 2935.00, 2940.00),
          ('Aug', 2936.00, 2940.00)

    SELECT MONTH,BILL_AMOUNT,PAID_AMOUNT FROM #billing

    SELECT 'Bill Amount'as Month,Jan,Dec,Nov,Oct,Sep,Aug
    FROM (
        SELECT BILL_AMOUNT,MONTH
        FROM #billing
    ) AS source_table
    PIVOT
    (
        SUM(BILL_AMOUNT)
        FOR MONTH
        in(Jan,Dec,Nov,Oct,Sep,Aug) 
    )AS Pivot_table

    Union all

    SELECT 'Paid Amount'as Month,Jan,Dec,Nov,Oct,Sep,Aug
    FROM (
            SELECT PAID_AMOUNT,MONTH
            FROM #billing
    ) AS source_table
    PIVOT
    (
        SUM(PAID_AMOUNT)
        FOR MONTH
        in(Jan,Dec,Nov,Oct,Sep,Aug) 
    )AS Pivot_table

Need help of pivot query in sql

2 Answers2