Pivot on changing columns

  • It doesn't look like Microsoft made it easy to pivot (like Matrix in SSRS) when the number of columns you end up with depends on the data.

    Using the below data.. if I run by Destination.. when I choose Russell I would need to place 281, 282, and 374 in the pivot statement, but if I am running for Raceland I only need 490, and LEX.

    The data I am working with will have between 10 and 75 columns.. and will depend on the data. I have not been able to find any solution that I can follow for how to pivot this data.

    Note that I am using a CTE to pull the data together.. so it looks like the below.. and would need to pivot this on Code.

    CREATE TABLE [dbo].[a_dcp](

    [Destination_TX] [varchar](100) NULL,

    [Code] [varchar](128) NULL,

    [Arrived] [int] NULL,

    [In_Trans] [int] NULL

    ) ON [PRIMARY]

    insert into a_dcp

    values ('Russell','281','0','1')

    insert into a_dcp

    values ('Russell','282','5','3')

    insert into a_dcp

    values ('Russell','281','9','1')

    insert into a_dcp

    values ('Ashland','281','11','9')

    insert into a_dcp

    values ('Ashland','374','1','15')

    insert into a_dcp

    values ('Ashland','490','10','1')

    insert into a_dcp

    values ('Ashland','Casco','1','1')

    insert into a_dcp

    values ('Raceland','490','13','12')

    insert into a_dcp

    values ('Raceland','Lex','30','22')

  • Check out the articles in my signature about cross tabs. In particular you will want to focus on the dynamic cross tab article. It should help get you pointed right where you need.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • This is as close and seems to work, now how would I show the Arrived .. In Transport. The columns are there.. Arrived first, and then In Transport, thanks to the Join.

    DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX), @HeadCol NVARCHAR(MAX), @HeadCol2 NVARCHAR(MAX);

    SET @columns = N'';

    set @HeadCol = N'';

    set @HeadCol2 = N'';

    select @HeadCol += N', aa.' + QUOTENAME(Code)

    FROM (SELECT p.code FROM a_dcp AS p

    GROUP BY p.code) AS x;

    select @HeadCol2 += N', aa.' + QUOTENAME(Code)

    FROM (SELECT p.code FROM a_dcp AS p

    GROUP BY p.code) AS x;

    SELECT @columns += N', p.' + QUOTENAME(Code)

    FROM (SELECT p.code FROM a_dcp AS p

    GROUP BY p.code) AS x;

    SET @sql = N'

    Select aa.Destination_TX, ' + STUFF(@Headcol, 1, 2, '') + ',' + STUFF(@Headcol2, 1, 2, '') + ' FROM (

    SELECT Destination_TX, ' + STUFF(@columns, 1, 2, '') + '

    FROM

    (

    SELECT Destination_TX, code, arrived

    FROM a_dcp

    ) p

    PIVOT

    (

    SUM(Arrived) FOR Code IN ('

    + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')

    + ')

    ) AS p

    ) as aa

    INNER JOIN (

    SELECT Destination_TX, ' + STUFF(@columns, 1, 2, '') + '

    FROM

    (

    SELECT Destination_TX, code, In_Trans

    FROM a_dcp

    ) p

    PIVOT

    (

    SUM(In_Trans) FOR Code IN ('

    + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')

    + ')

    ) AS p

    ) as bb

    on aa.Destination_TX = bb.Destination_TX';

    PRINT @sql;

    EXEC sp_executesql @sql;

  • Man this is messy. Has MS fixed this in 2012? We are using 2005 and 2008.

  • I modified the query so the columns will show if it is Arrived or In Transit, but the code works on my test... but not on my real data.

    Error:

    Msg 102, Level 15, State 1, Line 2

    Incorrect syntax near 'N'.

    Msg 156, Level 15, State 1, Line 13

    Incorrect syntax near the keyword 'as'.

    Msg 156, Level 15, State 1, Line 25

    Incorrect syntax near the keyword 'as'.

    Is there some cha, that I need to search my data for? That would crash the build of the SQL.

    DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX), @HeadCol NVARCHAR(MAX), @HeadCol2 NVARCHAR(MAX);

    SET @columns = N'';

    set @HeadCol = N'';

    set @HeadCol2 = N'';

    select @HeadCol += N', aa.' + QUOTENAME(Code) + ' as Arrived_' + Code

    FROM (SELECT p.code FROM a_DCP AS p

    GROUP BY p.code) AS x;

    select @HeadCol2 += N', aa.' + QUOTENAME(Code) + ' as In_Trans_' + Code

    FROM (SELECT p.code FROM a_DCP AS p

    GROUP BY p.code) AS x;

    SELECT @columns += N', p.' + QUOTENAME(Code)

    FROM (SELECT p.code FROM a_DCP AS p

    GROUP BY p.code) AS x;

    print @HeadCol

    SET @sql = N'

    Select aa.Destination_TX, ' + STUFF(@Headcol, 1, 2, '') + ',' + STUFF(@Headcol2, 1, 2, '') + ' FROM (

    SELECT Destination_TX, ' + STUFF(@columns, 1, 2, '') + '

    FROM

    (

    SELECT Destination_TX, code, arrived

    FROM a_DCP

    ) p

    PIVOT

    (

    SUM(Arrived) FOR Code IN ('

    + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')

    + ')

    ) AS p

    ) as aa

    INNER JOIN (

    SELECT Destination_TX, ' + STUFF(@columns, 1, 2, '') + '

    FROM

    (

    SELECT Destination_TX, code, In_Trans

    FROM a_DCP

    ) p

    PIVOT

    (

    SUM(In_Trans) FOR Code IN ('

    + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')

    + ')

    ) AS p

    ) as bb

    on aa.Destination_TX = bb.Destination_TX';

    PRINT @sql;

    EXEC sp_executesql @sql;

Viewing 5 posts - 1 through 4 (of 4 total)

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