dynamic pivot issue

  • sql_learner29

    SSCrazy Eights

    Points: 9107

    Hi

    I am trying to pivot data based on columns value in year column... but results are not showing up correctly..

    I want to see all columns after pivot

    I want to Pivot based on year shown in the data but it can be dynamic as year can go for last 3 years

    I am also using an inner join as i have two amount columns in my code and i want to show both amount columns for all displayed year...

    I am able to pivot but I need in output all the columns like this

    Id,MainDate, Year1,Year2,Year3(if any), AMT1 for YR1, AMT2 for Yr1, , AMT1 for YR2, AMT2 for Yr2, AMT1 for YR3, AMT2 for Yr3,

    Here is some data:

    -- CREATE TABLE [dbo].[TEMP](

    -- [FileType] [varchar](19) NOT NULL,

    -- [dType] [char](2) NOT NULL,

    -- [dVersion] [char](2) NOT NULL,

    -- [Id] [char](25) NOT NULL,

    -- [MainDate] [char](40) NULL,

    -- [Year] [char](30) NULL,

    -- [AMT1] [char](30) NULL,

    -- [AMT2] [char](30) NULL

    --)

    --SELECT * FROM [TEMP]

    -- INSERT INTO TEMP ( FileType,dType,dVersion,Id,MainDate,Year,AMT1,AMT2) VALUES ( 'ABC', '05', '01', '09000001 ', 'Mar 31, 2014 ', '2013 ', '41600.00 ', '2900.00 ')

    --INSERT INTO TEMP ( FileType,dType,dVersion,Id,MainDate,Year,AMT1,AMT2) VALUES ( 'ABC', '05', '01', '09000001 ', 'Mar 31, 2014 ', '2014 ', '52464.93 ', '2900.00 ')

    --INSERT INTO TEMP ( FileType,dType,dVersion,Id,MainDate,Year,AMT1,AMT2) VALUES ( 'ABC', '05', '01', '09000002 ', 'Mar 31, 2014 ' , '2013 ', '0.00 ', '2900.00 ')

    --INSERT INTO TEMP ( FileType,dType,dVersion,Id,MainDate,Year,AMT1,AMT2) VALUES ( 'ABC', '05', '01', '09000002 ', 'Mar 31, 2014 ', '2014 ', '21924.00 ', '2900.00 ')

    --INSERT INTO TEMP ( FileType,dType,dVersion,Id,MainDate,Year,AMT1,AMT2) VALUES ( 'ABC', '05', '01', '09000003 ', 'Mar 31, 2014 ', '2013 ', '122.00 ', '2900.00 ')

    --INSERT INTO TEMP ( FileType,dType,dVersion,Id,MainDate,Year,AMT1,AMT2) VALUES ( 'ABC', '05', '01', '09000003 ', 'Mar 31, 2014 ', '2014 ', '133.53 ', '2900.00 ')

    --INSERT INTO TEMP ( FileType,dType,dVersion,Id,MainDate,Year,AMT1,AMT2) VALUES ( 'ABC', '05', '01', '09000004 ', 'Mar 31, 2014 ', '2013 ', '8.00 ', '2900.00 ')

    --INSERT INTO TEMP ( FileType,dType,dVersion,Id,MainDate,Year,AMT1,AMT2) VALUES ( 'ABC', '05', '01', '09000004 ', 'Mar 31, 2014 ', '2014 ', '270.00 ', '2900.00 ')

    --INSERT INTO TEMP ( FileType,dType,dVersion,Id,MainDate,Year,AMT1,AMT2) VALUES ( 'ABC', '05', '01', '09000005 ', 'Mar 31, 2014 ', '2013 ', '715.00 ', '2900.00 ')

    --INSERT INTO TEMP ( FileType,dType,dVersion,Id,MainDate,Year,AMT1,AMT2) VALUES ( 'ABC', '05', '01', '09000005 ', 'Mar 31, 2014 ','2014 ', '175.00 ', '2900.00 ')

    DECLARE @cols AS NVARCHAR(MAX),

    @query AS NVARCHAR(MAX);

    SET @cols = STUFF(

    (SELECT distinct ',' + QUOTENAME(c.Year)

    FROM TEMP c

    FOR XML PATH(''), TYPE

    ).value('.', 'NVARCHAR(MAX)') ,1,1,''

    )

    --select @cols

    select * from

    (

    SELECT * from

    (

    select dType,dVersion,Id

    , AMT1,AMT2

    , Year

    from TEMP

    ) x

    pivot

    (

    max(AMT1)

    for Year in ( [2013 ],[2014 ] )

    ) p

    ) A

    inner join

    (

    SELECT * from

    (

    select dType,dVersion,Id

    , AMT2

    , Year

    from TEMP

    ) x

    pivot

    (

    max(AMT2)

    for Year in ( [2013 ],[2014 ] )

    ) p

    )B

    ON A.Id = B.Id

    any help on this?

    Thanks for your help

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • Luis Cazares

    SSC Guru

    Points: 183633

    This will generate the dynamic pivot for any number of years.

    Read more about it in the following article: http://www.sqlservercentral.com/articles/Crosstab/65048/

    DECLARE @Years nvarchar(4000) = '',

    @Amt1 nvarchar(4000) = '',

    @Amt2 nvarchar(4000) = '',

    @SQL nvarchar(MAX) ;

    --Generate column lists

    SELECT

    @Years = @Years + ',MAX( CASE WHEN Year = ' + CAST( Year AS char(4)) + ' THEN Year END) AS Year_' + CAST( Year AS char(4)) + CHAR(13)

    ,@Amt1 = @Amt1 + ',MAX( CASE WHEN Year = ' + CAST( Year AS char(4)) + ' THEN AMT1 END) AS AMT1_' + CAST( Year AS char(4)) + CHAR(13)

    ,@Amt2 = @Amt2 + ',MAX( CASE WHEN Year = ' + CAST( Year AS char(4)) + ' THEN AMT2 END) AS AMT2_' + CAST( Year AS char(4)) + CHAR(13)

    FROM [TEMP]

    GROUP BY Year

    ORDER BY Year;

    --Create complete SQL Statement

    SET @SQL = 'SELECT dType,dVersion,Id' + CHAR(13) + @Years + @Amt1 + @Amt2

    + 'FROM [TEMP] GROUP BY dType,dVersion,Id ';

    PRINT @SQL; --Test

    EXEC sp_Executesql @SQL --Execute

    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
  • sql_learner29

    SSCrazy Eights

    Points: 9107

    Wow...

    Works like a gem...

    Thanks a lot...

    but one question..

    as I am running the statement inside a S tored procedure and it will populate a temp table in my SP..

    Will the EXEC statement work inside SP too???

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • Luis Cazares

    SSC Guru

    Points: 183633

    A stored procedure (yours) can call another stored procedure (sp_executesql) without a problem.

    Do you want to insert the results on a temp table? That would cause more difficulties but it can be done.

    Do you want to insert data into a temp table to pivot it afterwards? yes, that shouldn't cause any trouble.

    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
  • sql_learner29

    SSCrazy Eights

    Points: 9107

    Hey Thanks...

    Let me modify my SP and give it a Try...

    Will comment soon..

    Thanks again

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • sql_learner29

    SSCrazy Eights

    Points: 9107

    Hi

    I was able to work with the code you provided for dynamic pivot successfully,inside my SP using a temp table to store the results using INTO statement

    like this:

    (MY_TEMP07)

    --Create complete SQL Statement

    SET @SQL = 'SELECT dType,dVersion,Id' + CHAR(13) + @Years + @Amt1 + @Amt2

    + 'INTO MY_TEMP07 FROM [TEMP] GROUP BY dType,dVersion,Id ';

    but today while testing it failed as for one ID's has got only Year 2014 data and nothing for previous 2 years..(so temp table did not create previous 2 years columns)

    So my insert statement below if giving me error:

    INSERT INTO MAINTABLE

    ( dType

    , dVersion

    , Id

    , maindate

    , PARA1_YEAR

    , PARA2_YEAR

    , PARA3_YEAR

    , PARA1_AMT1

    , PARA1_AMT2

    , PARA2_AMT1

    , PARA2_AMT2

    , PARA3_AMT1

    , PARA3_AMT2

    )

    select dType

    , dVersion

    , Id

    , maindate

    , coalesce([Year_2014] ,'')

    , coalesce([Year_2013] ,'')

    , coalesce([Year_2012] ,'')

    , coalesce([AMT1_2014],'')

    , coalesce([AMT2_2014],'')

    , coalesce([AMT1_2013],'')

    , coalesce([AMT2_2013],'')

    , coalesce([AMT1_2012],'')

    , coalesce([AMT2_2012],'')

    FROM MY_TEMP07

    so my insert inside SP is giving me this error...

    Msg 207, Level 16, State 1, Line 1582

    Invalid column name 'Year_2013'.

    Msg 207, Level 16, State 1, Line 1582

    Invalid column name 'Year_2013'.

    Msg 207, Level 16, State 1, Line 1588

    Invalid column name 'AMT1_2013'.

    Msg 207, Level 16, State 1, Line 1588

    Invalid column name 'AMT1_2013'.

    Msg 207, Level 16, State 1, Line 1589

    Invalid column name 'AMT2_2013'.

    Msg 207, Level 16, State 1, Line 1589

    Invalid column name 'AMT2_2013'.

    Is there anyway I can hard code AMT and YR column names generated from this query..

    I want only last 3 years 2012..2014..2013..

    I was also thinking of creating a dummy table with all columns(YR2014..YR2013...YR2012...AMT..) and inserting data whatever i got from dynamic query to it first then using that table in my final inserts

    Any help

    Thanks

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

Viewing 6 posts - 1 through 6 (of 6 total)

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