CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012pivot
bmsqldev
bmsqldev

Reputation: 2735

SQL Server pivot on multiple columns

I have a table which stores data hour-wise for a week.

This is the table structure (for one day):

dayId   HourId  amount  category
--------------------------------
1       9       1       a
1       9       1       b
1       9       1       c
1       9       1       d
1      10       1       a
1      10       1       b
1      10       1       c
1      10       1       d
1      11       1       a
1      11       1       b
1      11       0       c
1      11       1       d
1      12       0       a
1      12       2       b
1      12       2       c
1      12       2       d

I have used pivot to see day-wise amount distributions as below:

select 
    dayid,
    [9],
    [10],
    [11],
    [12]
from
    (select dayid, hourid, isnull(amount, 0) as amountfrom #test) as c
pivot
     (sum(amount) for hourid in ([9], [10], [11], [12])) as p

Output:

enter image description here

Now, I need to include categories to the amount distribution list. My expected output would be,

enter image description here

How to acheive the above result. please help...

Upvotes: 0

Views: 845

Answers (2)

Muhammad Azim
Muhammad Azim

Reputation: 329

IF you want dynamic pivot you can use bellow query 

CREATE Table #Item(dayid int,CatId varchar(20),value int)
INSERT INTO #Item
select dayid,Convert(varchar,hourId),sum(amount) from data a group by dayid,hourId
INSERT INTO #Item
select dayid,Convert(varchar,hourid)+Category,sum(amount) from data a group by dayid,hourId,Category Order by hourId
DECLARE @Query AS NVARCHAR(MAX) DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(CatId)
FROM (SELECT DISTINCT CatId FROM #Item) AS Category
SET @Query = N'SELECT dayid' + @ColumnName + 'FROM #Item PIVOT(SUM(value) FOR CatId IN (' + @ColumnName + ')) AS PVTTable'
EXEC sp_executesql @Query
Drop table #Item

Note: Output Column is not in proper order

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270873

Just use conditional aggregation:

select dayid,
       sum(case when hourid = 9 then amount else 0 end) as [9],
       sum(case when hourid = 9 and category = 'a' then amount else 0 end) as [a9],
       sum(case when hourid = 9 and category = 'b' then amount else 0 end) as [b9],
       . . .
from #test t
group by dayid;

Upvotes: 5

Related Questions