SQL Query Dynamic PIVOT

Question

Possible Duplicate:
SQL Server dynamic PIVOT query?

I have temporary table with following structure:

MONTH  ID           CNT
-----  -----------  ---
4      TOTAL_COUNT  214
5      TOTAL_COUNT  23
6      TOTAL_COUNT  23
4      FUNC_COUNT   47
5      FUNC_COUNT   5
6      FUNC_COUNT   5
4      INDIL_COUNT  167
5      INDIL_COUNT  18
6      INDIL_COUNT  18

How i can get the Pivot over month in this table like:

ID           APRIL  MAY  JUNE 
-----------  -----  ---  ----
TOTAL_COUNT  214    23   23
FUNC_COUNT   47     5    5
INDIL_COUNT  167    18   18

Please consider this table format. I am little messy in posting this format.

score 2 · Answer 1 · answered May 29 '12 at 04:41

2

I'll leave the conversion of month integers to month names to you, but this will perform the PIVOT for you.

declare @t table
( [month] int, [id] nvarchar(20), [cnt] int )

insert @t values (4,'TOTAL_COUNT',214)
insert @t values (5,'TOTAL_COUNT',23)
insert @t values (6,'TOTAL_COUNT',23)
insert @t values (4,'FUNC_COUNT',47)
insert @t values (5,'FUNC_COUNT',5)
insert @t values (6,'FUNC_COUNT',5)
insert @t values (4,'INDIL_COUNT',167)
insert @t values (5,'INDIL_COUNT',18)
insert @t values (6,'INDIL_COUNT',18)

SELECT 
    [id], [4], [5], [6]
FROM
    (SELECT [month], [id], [cnt] FROM @t) src
PIVOT
    (SUM([cnt]) FOR [month] IN ([4], [5], [6])) p

answered May 29 '12 at 04:41

Kirk Broadhurst

27,836
16
104
169

On second thought, you just need `SELECT [id], [4] as [April], [5] as [May], [6] as [June]` ! – Kirk Broadhurst May 29 '12 at 04:42
Thanks for your answer i was expecting simillar kind of answer But the [MONTH] field is flexible so the Select statement is always giving the constant results the month value can be JAN,FEB March also.How can i generate those dynamically – Pratik May 29 '12 at 04:54
4

Dynamic PIVOT is a much more complicated thing to do. If you are not confident with PIVOT then I would strongly suggest against dynamic PIVOT - it involves dynamic SQL and is a big headache. Normally this is a reporting or visualisation requirement, so I would suggest you do this formatting in your presentation layer. – Kirk Broadhurst May 29 '12 at 04:59
Seems a bit complex solution to me i think i need to manage this in my SSRS report only.Thanks though @Krik – Pratik May 29 '12 at 06:32
If you are using SSRS then it is much preferred to do this using Column Groups. – Kirk Broadhurst May 29 '12 at 06:36

Taryn · Accepted Answer · 2012-05-29T15:20:19.143

While you can use a Static Pivot - one that you hard-code the months. In the comments, you stated that the number of months maybe be unknown, if that is the case then you will want to use a Dynamic Pivot to generate the list of months. Using a Dynamic Pivot gives you the flexibility of not knowing the columns you need until you run it.

create table t
( 
    [month] int, 
    [id] nvarchar(20), 
    [cnt] int 
)

insert t values (4,'TOTAL_COUNT',214)
insert t values (5,'TOTAL_COUNT',23)
insert t values (6,'TOTAL_COUNT',23)
insert t values (4,'FUNC_COUNT',47)
insert t values (5,'FUNC_COUNT',5)
insert t values (6,'FUNC_COUNT',5)
insert t values (4,'INDIL_COUNT',167)
insert t values (5,'INDIL_COUNT',18)
insert t values (6,'INDIL_COUNT',18)

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(month) 
            FROM t 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT id, ' + @cols + ' from 
            (
                select month, id, cnt
                from t
           ) x
            pivot 
            (
                 sum(cnt)
                for month in (' + @cols + ')
            ) p '


execute(@query)

drop table t

The results would be:

enter image description here

Thanks a lot @Bluefeet. It's just solved the problem. – Pratik May 30 '12 at 04:22 — Pratik, May 30 '12 at 04:22

SQL Query Dynamic PIVOT

2 Answers2

Linked