Results 1 to 13 of 13

Thread: [RESOLVED] How to pivot a result set

  1. #1

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,644

    Resolved [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.
    Slow as hell.

  2. #2
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,612

    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.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  3. #3

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,644

    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
    Slow as hell.

  4. #4
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,957

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,644

    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.
    Slow as hell.

  6. #6
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,957

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,644

    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.
    Slow as hell.

  8. #8
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,612

    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.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  9. #9

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,644

    Re: How to pivot a result set

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

  10. #10

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,644

    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?
    Slow as hell.

  11. #11

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,644

    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
    Slow as hell.

  12. #12
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,957

    Re: [RESOLVED] How to pivot a result set

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

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  13. #13

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,644

    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.
    Slow as hell.

Posting Permissions

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



Click Here to Expand Forum to Full Width