Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 How to pivot a result set.

Author  Topic 

sapator
Constraint Violating Yak Guru

462 Posts
Posted - 2015-01-14 : 05:18:06
Hi.
I am trying to pivot what i have found on the following result set:

select top 100 Item_strItemDescription
,
CASE WHEN DATEPART(hour,TransI_dtmRealTransTime) = 1 THEN '1am-2'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 2 THEN '2am-3'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 3 THEN '3am-4'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 4 THEN '4am-5'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 5 THEN '5am-6'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 6 THEN '6am-7'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 7 THEN '7am-8'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 8 THEN '8am-9'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 9 THEN '9am-10'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 10 THEN '10am-11'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 11 THEN '11am-12'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 12 THEN 'Noon-1'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 13 THEN '1pm-2'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 14 THEN '2pm-3'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 15 THEN '3pm-4'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 16 THEN '4pm-5'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 17 THEN '5pm-6'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 18 THEN '6pm-7'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 19 THEN '7pm-8'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 20 THEN '8pm-9'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 21 THEN '9pm-10'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 22 THEN '10pm-11'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 23 THEN '11pm-12'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 0 THEN 'Midnight-1' end as HoursT
,sum(TransI_curFullPrice)
from 
tblTrans_Inventory t inner join tblitem I on t.Item_strItemId = I.Item_strItemId
where transI_strtype = 'S'
and I.Item_strItemId in ( '8792','6','38')
group by  DATEPART(hour,TransI_dtmRealTransTime) ,
Item_strItemDescription
order by DATEPART(hour,TransI_dtmRealTransTime)



This will display
'item1'-'1am-2' - '33,44'
'item1'-'4am-5' - '30,44'
'item2'-'5am-6' - '133,44'
'item2'-'10am-11' - '33,44'
'item3'-'1am-2' - '313,44'
etc.


I am trying to go like this:
1am-2 2am -3 3am-4 4am -5 etc...
item1 33,44 -- 44,55
item2 ----- 44,66
item3 11,44 ----- 66,77

Any ideas how to pivot this?

I did this but i get the items replicated
1am2 2am-3 3am 4am-5
item1 33,44
item1 ---- 55,66
item1 ---------- 77,44
item2 11,11
item2 ------ 12,34
item3 33,33
item3 --------- 44,44





select top 100 Item_strItemDescription
,
  SUM(CASE WHEN DATEPART(hour,TransI_dtmRealTransTime) = 1 THEN TransI_curFullPrice ELSE 0 END) AS '1am-2',
       SUM(CASE WHEN DATEPART(hour,TransI_dtmRealTransTime) = 2 THEN TransI_curFullPrice ELSE 0 END) AS '2am-3',
       SUM(CASE WHEN DATEPART(hour,TransI_dtmRealTransTime) = 3 THEN TransI_curFullPrice ELSE 0 END) AS '3am-4',
       SUM(CASE WHEN DATEPART(hour,TransI_dtmRealTransTime) = 4 THEN TransI_curFullPrice ELSE 0 END) AS '4am-5',
       SUM(CASE WHEN DATEPART(hour,TransI_dtmRealTransTime) = 5 THEN TransI_curFullPrice ELSE 0 END) AS '5am-6',
       SUM(CASE WHEN DATEPART(hour,TransI_dtmRealTransTime) = 6 THEN TransI_curFullPrice ELSE 0 END) AS '6am-7',
       SUM(CASE WHEN DATEPART(hour,TransI_dtmRealTransTime) = 7 THEN TransI_curFullPrice ELSE 0 END) AS '7am-8',
       SUM(CASE WHEN DATEPART(hour,TransI_dtmRealTransTime) = 8 THEN TransI_curFullPrice ELSE 0 END) AS '8am-9',
       SUM(CASE WHEN DATEPART(hour,TransI_dtmRealTransTime) = 9 THEN TransI_curFullPrice ELSE 0 END) AS '9am-10',
       SUM(CASE WHEN DATEPART(hour,TransI_dtmRealTransTime) = 10 THEN TransI_curFullPrice ELSE 0 END) AS '10am-11',
	   SUM(CASE WHEN DATEPART(hour,TransI_dtmRealTransTime) = 11 THEN TransI_curFullPrice ELSE 0 END) AS '11am-12',     
	   SUM(CASE WHEN DATEPART(hour,TransI_dtmRealTransTime) = 12 THEN TransI_curFullPrice ELSE 0 END) AS 'Noon-1',
	     SUM(CASE WHEN DATEPART(hour,TransI_dtmRealTransTime) = 13 THEN TransI_curFullPrice ELSE 0 END) AS '1pm-2',
	   SUM(CASE WHEN DATEPART(hour,TransI_dtmRealTransTime) = 14 THEN TransI_curFullPrice ELSE 0 END) AS '2pm-3',
	   SUM(CASE WHEN DATEPART(hour,TransI_dtmRealTransTime) = 15 THEN TransI_curFullPrice ELSE 0 END) AS '3pm-4',
		SUM(CASE WHEN DATEPART(hour,TransI_dtmRealTransTime) = 16 THEN TransI_curFullPrice ELSE 0 END) AS '4pm-5',
		SUM(CASE WHEN DATEPART(hour,TransI_dtmRealTransTime) = 17 THEN TransI_curFullPrice ELSE 0 END) AS '5pm-6',
	    SUM(CASE WHEN DATEPART(hour,TransI_dtmRealTransTime) = 18 THEN TransI_curFullPrice ELSE 0 END) AS '6pm-7',
		SUM(CASE WHEN DATEPART(hour,TransI_dtmRealTransTime) = 19 THEN TransI_curFullPrice ELSE 0 END) AS '7pm-8',
		SUM(CASE WHEN DATEPART(hour,TransI_dtmRealTransTime) = 20 THEN TransI_curFullPrice ELSE 0 END) AS '8pm-9',
		SUM(CASE WHEN DATEPART(hour,TransI_dtmRealTransTime) = 21 THEN TransI_curFullPrice ELSE 0 END) AS '9pm-10',
		SUM(CASE WHEN DATEPART(hour,TransI_dtmRealTransTime) = 22 THEN TransI_curFullPrice ELSE 0 END) AS '10pm-11',
		SUM(CASE WHEN DATEPART(hour,TransI_dtmRealTransTime) = 23 THEN TransI_curFullPrice ELSE 0 END) AS '11pm-12'
		from 
tblTrans_Inventory t inner join tblitem I on t.Item_strItemId = I.Item_strItemId
where transI_strtype = 'S'
and I.Item_strItemId in ( '8792','6','38')
group by  DATEPART(hour,TransI_dtmRealTransTime) ,
Item_strItemDescription
order by DATEPART(hour,TransI_dtmRealTransTime)

sapator
Constraint Violating Yak Guru

462 Posts
Posted - 2015-01-14 : 07:24:39
Will work if i remove the group by DATEPART(hour,TransI_dtmRealTransTime) and the order by
Now i try with 2 pivots. The first return null values the second puts the times in the sum columns.
What do you think?
Thanks
SELECT *
FROM (
    SELECT top 100  Item_strItemDescription as Item,  CASE WHEN DATEPART(hour,TransI_dtmRealTransTime) = 1 THEN '1am-2'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 2 THEN '2am-3'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 3 THEN '3am-4'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 4 THEN '4am-5'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 5 THEN '5am-6'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 6 THEN '6am-7'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 7 THEN '7am-8'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 8 THEN '8am-9'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 9 THEN '9am-10'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 10 THEN '10am-11'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 11 THEN '11am-12'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 12 THEN 'Noon-1'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 13 THEN '1pm-2'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 14 THEN '2pm-3'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 15 THEN '3pm-4'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 16 THEN '4pm-5'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 17 THEN '5pm-6'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 18 THEN '6pm-7'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 19 THEN '7pm-8'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 20 THEN '8pm-9'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 21 THEN '9pm-10'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 22 THEN '10pm-11'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 23 THEN '11pm-12'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 0 THEN 'Midnight-1' end as HoursT, 
        TransI_curFullPrice as Amount 
  from 
tblTrans_Inventory t inner join tblitem I on t.Item_strItemId = I.Item_strItemId
where transI_strtype = 'S'
and I.Item_strItemId in ( '8792','6','38')
--group by  DATEPART(hour,TransI_dtmRealTransTime) ,
--Item_strItemDescription,  TransI_curFullPrice
) as s
PIVOT
(
    SUM(Amount)
    FOR [HoursT] IN (['1am-2'],['2am-3'],['3am-4'],['4am-5'],['5am-6'],['6am-7'],['7am-8'],['8am-9'],['9am-10'],['10am-11'],['11am-12'],['Noon-1']
	,['1pm-2'],['2pm-3'],['3pm-4'], ['4pm-5'],['5pm-6'],['6pm-7'],['7pm-8'],['8pm-9'],['9pm-10'],['10pm-11'],['11pm-12'],['Midnight-1'])
)AS pvt






SELECT Item_strItemDescription, '1am-2','2am-3','3am-4','4am-5','5am-6','6am-7','7am-8','8am-9','9am-10','10am-11','11am-12','Noon-1'
	,'1pm-2','2pm-3','3pm-4', '4pm-5','5pm-6','6pm-7','7pm-8','8pm-9','9pm-10','10pm-11','11pm-12','Midnight-1'
FROM 
(SELECT Item_strItemDescription,  CASE WHEN DATEPART(hour,TransI_dtmRealTransTime) = 1 THEN '1am-2'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 2 THEN '2am-3'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 3 THEN '3am-4'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 4 THEN '4am-5'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 5 THEN '5am-6'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 6 THEN '6am-7'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 7 THEN '7am-8'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 8 THEN '8am-9'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 9 THEN '9am-10'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 10 THEN '10am-11'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 11 THEN '11am-12'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 12 THEN 'Noon-1'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 13 THEN '1pm-2'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 14 THEN '2pm-3'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 15 THEN '3pm-4'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 16 THEN '4pm-5'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 17 THEN '5pm-6'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 18 THEN '6pm-7'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 19 THEN '7pm-8'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 20 THEN '8pm-9'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 21 THEN '9pm-10'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 22 THEN '10pm-11'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 23 THEN '11pm-12'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 0 THEN 'Midnight-1' end as HoursT,  TransI_curFullPrice as x
  from 
tblTrans_Inventory t inner join tblitem I on t.Item_strItemId = I.Item_strItemId
where transI_strtype = 'S'
and I.Item_strItemId in ( '8792','6','38') ) ps
PIVOT
(
SUM (x)
FOR HoursT IN
( ['1am-2'],['2am-3'],['3am-4'],['4am-5'],['5am-6'],['6am-7'],['7am-8'],['8am-9'],['9am-10'],['10am-11'],['11am-12'],['Noon-1']
	,['1pm-2'],['2pm-3'],['3pm-4'], ['4pm-5'],['5pm-6'],['6pm-7'],['7pm-8'],['8pm-9'],['9pm-10'],['10pm-11'],['11pm-12'],['Midnight-1'])
) AS pvt
Go to Top of Page

sapator
Constraint Violating Yak Guru

462 Posts
Posted - 2015-01-16 : 04:08:00
Fixed!

SELECT *
FROM (
    SELECT Item_strItemDescription as Item,  CASE WHEN DATEPART(hour,TransI_dtmRealTransTime) = 0 THEN 'Midnight-1'
	WHEN DATEPART(hour,TransI_dtmRealTransTime) = 1 THEN '1am-2'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 2 THEN '2am-3'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 3 THEN '3am-4'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 4 THEN '4am-5'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 5 THEN '5am-6'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 6 THEN '6am-7'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 7 THEN '7am-8'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 8 THEN '8am-9'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 9 THEN '9am-10'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 10 THEN '10am-11'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 11 THEN '11am-12'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 12 THEN 'Noon-1'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 13 THEN '1pm-2'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 14 THEN '2pm-3'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 15 THEN '3pm-4'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 16 THEN '4pm-5'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 17 THEN '5pm-6'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 18 THEN '6pm-7'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 19 THEN '7pm-8'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 20 THEN '8pm-9'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 21 THEN '9pm-10'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 22 THEN '10pm-11'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 23 THEN '11pm-12' end as HoursT, 
        TransI_curNetTotal as Amount 
  from 
tblTrans_Inventory t inner join tblitem I on t.Item_strItemId = I.Item_strItemId
and TransI_dtmRealTransTime between '20140101' and '20150101'
and I.Item_strItemId in ('8792','6','38')

--and i.item_strstatus = 'A'
--group by  DATEPART(hour,TransI_dtmRealTransTime) ,
--Item_strItemDescription,  TransI_curFullPrice
) s
PIVOT
(
    SUM(Amount)
    FOR [HoursT] IN ([Midnight-1],[1am-2],[2am-3],[3am-4],[4am-5],[5am-6],[6am-7],[7am-8],[8am-9],[9am-10],[10am-11],[11am-12],[Noon-1]
	,[1pm-2],[2pm-3],[3pm-4], [4pm-5],[5pm-6],[6pm-7],[7pm-8],[8pm-9],[9pm-10],[10pm-11],[11pm-12])
)AS pvt



SELECT Item_strItemDescription,[1am-2],[2am-3],[3am-4],[4am-5],[5am-6],[6am-7],[7am-8],[8am-9],[9am-10],[10am-11],[11am-12],[Noon-1]
	,[1pm-2],[2pm-3],[3pm-4], [4pm-5],[5pm-6],[6pm-7],[7pm-8],[8pm-9],[9pm-10],[10pm-11],[11pm-12],[Midnight-1]
FROM 
(SELECT Item_strItemDescription,  CASE WHEN DATEPART(hour,TransI_dtmRealTransTime) = 0 THEN 'Midnight-1' 
 WHEN DATEPART(hour,TransI_dtmRealTransTime) = 1 THEN '1am-2'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 2 THEN '2am-3'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 3 THEN '3am-4'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 4 THEN '4am-5'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 5 THEN '5am-6'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 6 THEN '6am-7'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 7 THEN '7am-8'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 8 THEN '8am-9'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 9 THEN '9am-10'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 10 THEN '10am-11'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 11 THEN '11am-12'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 12 THEN 'Noon-1'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 13 THEN '1pm-2'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 14 THEN '2pm-3'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 15 THEN '3pm-4'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 16 THEN '4pm-5'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 17 THEN '5pm-6'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 18 THEN '6pm-7'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 19 THEN '7pm-8'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 20 THEN '8pm-9'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 21 THEN '9pm-10'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 22 THEN '10pm-11'
WHEN DATEPART(hour,TransI_dtmRealTransTime) = 23 THEN '11pm-12' end as HoursT,  TransI_curNetTotal as x
  from 
tblTrans_Inventory t inner join tblitem I on t.Item_strItemId = I.Item_strItemId
where transI_strtype = 'S'
and TransI_dtmRealTransTime between '20140101' and '20150101'
and I.Item_strItemId in ( '8792','6','38') ) ps
PIVOT
(
SUM (x)
FOR HoursT IN
( [1am-2],[2am-3],[3am-4],[4am-5],[5am-6],[6am-7],[7am-8],[8am-9],[9am-10],[10am-11],[11am-12],[Noon-1]
	,[1pm-2],[2pm-3],[3pm-4], [4pm-5],[5pm-6],[6pm-7],[7pm-8],[8pm-9],[9pm-10],[10pm-11],[11pm-12],[Midnight-1])
) AS pvt
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site