# Thread: [RESOLVED] How to pivot a result set

1. ## [RESOLVED] How to pivot a result set

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

Code:
```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

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)```
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.
Thanks.

2. ## Re: How to pivot a result set

I think all you need to do is change the group by clause to remove DATEPART(hour,TransI_dtmRealTransTime). As long as you're grouping by the hour you will get a row for each hour. If you stop grouping by that the rows will get rolled up into the SUM. so you want :-
group by Item_strItemDescription

You'll also need to remove it from the Order By .

I would recommend persevering with the pivot statement though. It is tricky to get your head around it at first but once you do it generally performs better that a Case with aggregates and it's more readable over large numbers of columns too. This page gives a good explanation and some decent examples.

3. ## Re: How to pivot a result set

Yep, works fine.
As for pivoting i am doing 2 pivots both not working. First will bring null values and the second will put the times in the line of the sums i wanted.
You can take a look if you like. Thanks.

Code:
```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```

4. ## Re: How to pivot a result set

" i am doing 2 pivots both not working" --- really? You're going to leave it at that? Did your fingers fall off or something?

your select seems overcomplicated... you only need to do the datepart once...
Code:
```    SELECT top 100  Item_strItemDescription as Item,
CASE DATEPART(hour,TransI_dtmRealTransTime)
WHEN 1 THEN '1am-2'
WHEN 2 THEN '2am-3'
WHEN 3 THEN '3am-4'
WHEN 4 THEN '4am-5'
WHEN 5 THEN '5am-6'
WHEN 6 THEN '6am-7'
WHEN 7 THEN '7am-8'```
Since you're comparing the samething on each when line, there's no need to repeat it.

I've done a few pivots before, I've always struggled with them. There's something that feels wrong about yours, but honestly, I can't quite place my finger on it.

-tg

5. ## Re: How to pivot a result set

Lol. I'm leaving it at that since the company i work for has only one software engineer and one full admin part sql software engineer that must serve close to 250 people. AKA i don't have time to try something. If the first non pivot worked then let it be for now and i will try to pivot if i have time(you know, do da pivot. We all do da pivot). Yes the pivot feels wrong and since i don't do the pivot i don't know the exact issue. I'm guessing it's a close call though.
I'm not sure about the datepart once that you said? How would i do it once since i need to set the times for the whole day? If you mean the repeat on the 2nd pivot, that is the second example i tried, it's individual to the first one.

6. ## Re: How to pivot a result set

The comment about the datepart is outside the pivot issue... and I included a sample of how to fix it.... it's just like a Case in VB or Switch in C/C++/C# ... call DATEPART only once... then match where the result is 1,2,3,4,5...

anyways, the reason I can't figure what's wrong is because as far as I can tell it's syntatically right... but w/o sample data or an idea what kind of errors your'e getting - or is it not that you're getting errors, but rahter just not the right data?

-tg

7. ## Re: How to pivot a result set

Ah, ok i just saw that about the case.
Also the samples will "work" but as i've said the first will bring null's and the second just put the dates into where the sums rows where supposed to be.

8. ## Re: How to pivot a result set

The first is probably returning nulls because there is no row that gives a value to that tuple. So if there's no record for a widget at 9am then you will see a null for widgets at 9am.

I can't see anything obviously wrong with the second one but, as TG said, it's very difficult to say without sample data.

9. ## Re: How to pivot a result set

I will try to post some sample when i have time but it may take some time...

10. ## Re: How to pivot a result set

So on the second one, if i do this:
Code:
```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 TransI_dtmRealTransTime between '20140101' and '20140901'
and I.Item_strItemId in ( '398','68','90')```
It will bring the data like this
Item_strItemDescription HoursT x
AVRA ΝΕΡΟ (0,50lt) 8pm-9 0,35
AVRA ΝΕΡΟ (0,50lt) 9pm-10 0,35
AVRA ΝΕΡΟ (0,50lt) 9pm-10 0,35
AVRA ΝΕΡΟ (0,50lt) 9pm-10 0,35
AVRA ΝΕΡΟ (0,50lt) 11pm-12 0,35
AVRA ΝΕΡΟ (0,50lt) 9pm-10 0,35
AVRA ΝΕΡΟ (0,50lt) 9pm-10 0,35
AVRA ΝΕΡΟ (0,50lt) 10pm-11 0,35
Medium Fanta 10pm- 11 0,89
so the data is there.
Now when i run the complete pivot i get this:
Item_strDescription - nocolumnname -nocolumnname - nocolumnname ----etc
AVRA ΝΕΡΟ (0,50lt) -1am-2 ----- 2am-3 -- --- - 3am-4 ----etc

It's like the sum is totally lost and replaced with hours!? Oh my sum where are thou?

11. ## Re: How to pivot a result set

Fixed them both!
Can you spot the differences?
Code:
```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```

12. ## Re: [RESOLVED] How to pivot a result set

Selecting column names rather than literal text... seems obvious once you see it... :P

-tg

13. ## Re: [RESOLVED] How to pivot a result set

Yeah, but again a lesson for me not to repeat.
If this have any advanced performance rather that the initial solution i would consider changing it.
If not so much then i rather don't since the thing travel to 11 different server databases across the country collecting data, so i much rather save work time if it's better, or not that better.
Thanks.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•