Hi.

I am trying to pivot what i have found on the following result set:

This will displayCode: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)

'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

I guess pivot command ought to do the trick but i am struggling and bending my neck horizontally to try to figure how to play with it.Code: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)

Thanks.