Help with a PIVOT

  • Hi All

    I am having a bit of nightmare with a pivot query. When I say nightmare... I am nearly there but it is driving me crazy.

    To reproduce - create the following table. Using the attached script.

    Then I query PIVOT on the first 3 rows - these are the only rows I am interested in:

    Using this query: The first query before the cte is just for reference.

    select top 3

    id,Exsubcat,Sales1,Sales2,Sales3,Sales4,Sales5

    from BusinessExpense

    ;

    with bah (id,ExSubCat,Sales1,Sales2,Sales3,Sales4,Sales5,RowId)

    as

    (

    select Id,ExSubCat,Sales1,Sales2,Sales3,Sales4,Sales5, ROW_NUMBER() over (partition by(Sales1) order by id)

    as RowId from BusinessExpense

    )

    select *

    from

    (select Sales1,ExSubCat,RowId

    from bah) c

    pivot

    (

    max(Sales1)

    for ExSubCat IN (

    [ENTER TCO NAME]

    ,[ENTER ACC NUMBER]

    ,[ENTER ACC NAME]

    )

    ) as pivottable;

    The first row is fine in the return, however I can't get Sales2, Sales3, Sales4, Sales5

    Any help greatly received.

  • You haven't supplied the expected output, but I suspect you are trying to transpose the data rather than pivot the data. You would need to unpivot followed by a pivot.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Hi Drew

    I thought I had included the expected output as an image.

    You are correct it's wrong of me to call this a PIVOT in the truest term, it is a transpose.

    Thanks anyway.

  • Is this what you need? Note that instead of pivot I'm using cross tabs and instead of unpivot I'm using CROSS APPLY.

    SELECT RowId,

    MAX( CASE WHEN ExSubCat = 'ENTER TCO NAME' THEN Sales END) AS [ENTER TCO NAME],

    MAX( CASE WHEN ExSubCat = 'ENTER ACC NUMBER' THEN Sales END) AS [ENTER ACC NUMBER],

    MAX( CASE WHEN ExSubCat = 'ENTER ACC NAME' THEN Sales END) AS [ENTER ACC NAME]

    FROM BusinessExpense

    CROSS APPLY (VALUES( 1, Sales1),

    ( 2, Sales2),

    ( 3, Sales3),

    ( 4, Sales4),

    ( 5, Sales5))u(RowId, Sales)

    GROUP BY RowId;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Thank you Louis - I haven't used CROSS TAB before, makes life a lot easier than trying to use something not designed for transposing.

    This is all I need I think.

    Thank you again

    P

  • pnr8uk (8/3/2016)


    Thank you Louis - I haven't used CROSS TAB before, makes life a lot easier than trying to use something not designed for transposing.

    This is all I need I think.

    Thank you again

    P

    Great! Now, the big question: Do you understand it?

    If you don't you should ask more questions.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • I understand - it works for what I want.

    I will now educate myself on the query itself, take it apart etc, put it in my armoury

    Thanks you sir

  • pnr8uk (8/3/2016)


    What does the u in this line mean?

    ( 5, Sales5))u(RowId, Sales)

    Thanks again

    Paul

    The u is a table alias (even if it's not a table, but a Table Value Constructor). I used "u" for Unpivot.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares (8/3/2016)


    pnr8uk (8/3/2016)


    What does the u in this line mean?

    ( 5, Sales5))u(RowId, Sales)

    Thanks again

    Paul

    The u is a table alias (even if it's not a table, but a Table Value Constructor). I used "u" for Unpivot.

    Isn't it? I mean, why do you think they call it a TABLE Value Constructor? It's a table constructed from a set of values.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen (8/3/2016)


    Luis Cazares (8/3/2016)


    pnr8uk (8/3/2016)


    What does the u in this line mean?

    ( 5, Sales5))u(RowId, Sales)

    Thanks again

    Paul

    The u is a table alias (even if it's not a table, but a Table Value Constructor). I used "u" for Unpivot.

    Isn't it? I mean, why do you think they call it a TABLE Value Constructor? It's a table constructed from a set of values.

    Drew

    It acts like a table, but it's just an expression. It's the same as table-valued functions and common table expressions. None of them are tables or store data in them, they just act as tables.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Read Kenneth Fisher's blog[/url] about it.

  • Luis Cazares (8/3/2016)


    drew.allen (8/3/2016)


    Luis Cazares (8/3/2016)


    pnr8uk (8/3/2016)


    What does the u in this line mean?

    ( 5, Sales5))u(RowId, Sales)

    Thanks again

    Paul

    The u is a table alias (even if it's not a table, but a Table Value Constructor). I used "u" for Unpivot.

    Isn't it? I mean, why do you think they call it a TABLE Value Constructor? It's a table constructed from a set of values.

    Drew

    It acts like a table, but it's just an expression. It's the same as table-valued functions and common table expressions. None of them are tables or store data in them, they just act as tables.

    Maybe it acts like a table, because it is a table. It has rows, it has columns. Isn't that enough to make it a table? Views are included in INFORMATION_SCHEMA.TABLES, because they are tables in this general sense. You can't store data in the results of a join, but that's still a table--albeit a virtual one.

    I also have no problem with TABLE having two different definitions, in the same way that I have no problem with CAT having two different definitions, because one is a specific instance of a more general group in both cases.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen (8/3/2016)


    Luis Cazares (8/3/2016)


    drew.allen (8/3/2016)


    Luis Cazares (8/3/2016)


    pnr8uk (8/3/2016)


    What does the u in this line mean?

    ( 5, Sales5))u(RowId, Sales)

    Thanks again

    Paul

    The u is a table alias (even if it's not a table, but a Table Value Constructor). I used "u" for Unpivot.

    Isn't it? I mean, why do you think they call it a TABLE Value Constructor? It's a table constructed from a set of values.

    Drew

    It acts like a table, but it's just an expression. It's the same as table-valued functions and common table expressions. None of them are tables or store data in them, they just act as tables.

    Maybe it acts like a table, because it is a table. It has rows, it has columns. Isn't that enough to make it a table? Views are included in INFORMATION_SCHEMA.TABLES, because they are tables in this general sense. You can't store data in the results of a join, but that's still a table--albeit a virtual one.

    I also have no problem with TABLE having two different definitions, in the same way that I have no problem with CAT having two different definitions, because one is a specific instance of a more general group in both cases.

    Drew

    I distinguish a 'table' from a record set. I let Sql Server define a table as an object in sys.tables. Though these and CTEs, joins, etc... manifest themselves as record sets in the internals of Sql Server. Record set are tabular of course. It depends on perspective.

    ----------------------------------------------------

  • VALUES is not a table. It is a table value constructor, because the values clause can contain expressions which are used to construct the actual values produced. Tables can have values in columns, even computed values in computed columns. But you can't cross join to a table and do this:

    declare @input table(Amount int)

    insert into @input

    select 36 union all

    select 16

    select * from @input

    -- the value in the Result column is constructed

    select Amount, v.Result

    from @input

    cross apply (values (Amount),(Amount + 1), (Amount * 10), (Amount+100), (Sqrt(Amount)), (Amount/2)) v (Result)

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply