How to use dynamic pivot on this data

  • I've tried to understand dynamic sql but, to no avail. Could you tell me how to write the code that will pivot the diagnoses and use the DiagnosisUrnID as column headings. Maybe Diag1, Diag2, Diag3........
    Then, a short description of what the different steps of the query are doing?

    create table #T

    (

    account_num varchar(10),

    DiagnosisUrnID int,

    Diagnosis varchar(10)

    )

    insert into #T(account_num, DiagnosisUrnID, Diagnosis) values('a1111',1,'Z12.11')

    insert into #T(account_num, DiagnosisUrnID, Diagnosis) values('a1112',2,'K62.1')

    insert into #T(account_num, DiagnosisUrnID, Diagnosis) values('a1113',3,'D12.3')

    insert into #T(account_num, DiagnosisUrnID, Diagnosis) values('a1114',4,'D12.2')

    insert into #T(account_num, DiagnosisUrnID, Diagnosis) values('a1115',5,'K57.30')

    insert into #T(account_num, DiagnosisUrnID, Diagnosis) values('a1116',6,'K64.0')

    insert into #T(account_num, DiagnosisUrnID, Diagnosis) values('a1117',7,'I10')

    insert into #T(account_num, DiagnosisUrnID, Diagnosis) values('a1118',8,'E78.5')

    insert into #T(account_num, DiagnosisUrnID, Diagnosis) values('a1119',9,'E66.9')

    insert into #T(account_num, DiagnosisUrnID, Diagnosis) values('a1120',10,'Z68.30')

    insert into #T(account_num, DiagnosisUrnID, Diagnosis) values('a1121',11,'F32.9')

    insert into #T(account_num, DiagnosisUrnID, Diagnosis) values('b2222',1,'M65.331')

    insert into #T(account_num, DiagnosisUrnID, Diagnosis) values('b2223',2,'E11.40')

    insert into #T(account_num, DiagnosisUrnID, Diagnosis) values('b2224',3,'Z79.84')

    insert into #T(account_num, DiagnosisUrnID, Diagnosis) values('c3333',1,'Z30.432')

    insert into #T(account_num, DiagnosisUrnID, Diagnosis) values('c3334',2,'F17.210')

    insert into #T(account_num, DiagnosisUrnID, Diagnosis) values('d4444',1,'G56.21')

    insert into #T(account_num, DiagnosisUrnID, Diagnosis) values('d4445',2,'G56.01')

    insert into #T(account_num, DiagnosisUrnID, Diagnosis) values('d4446',3,'F17.210')

    insert into #T(account_num, DiagnosisUrnID, Diagnosis) values('d4447',4,'I10')

    insert into #T(account_num, DiagnosisUrnID, Diagnosis) values('d4448',5,'E78.5')

    insert into #T(account_num, DiagnosisUrnID, Diagnosis) values('d4449',6,'E11.42')

    insert into #T(account_num, DiagnosisUrnID, Diagnosis) values('d4450',7,'Z79.84')

  • With the data you've provided, could you show what you want your expected result set to be?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Step by step dynamic pivot script? Your wish is my command.  It's all out there for you if you search for it.

    John

  • Thom A - Friday, February 2, 2018 8:51 AM

    With the data you've provided, could you show what you want your expected result set to be?

    Attached is what I need it to look like.
    Thanx.

  • John Mitchell-245523 - Friday, February 2, 2018 8:52 AM

    Step by step dynamic pivot script? Your wish is my command.  It's all out there for you if you search for it.

    John

    Sorry for imposing. Some of us struggle with this stuff. I've found several examples and explanations but, there's nothing like having someone explain, using your own example.

  • You're not imposing.  It's just that it's easier to assist someone who wants help with a specific issue, rather than asking for an end-to-end solution.  You should try working through that article I linked to - you may find it's closer to your situation than you imagine.  And if it isn't, then please do ask for help.

    By the way, might it be possible to put an upper limit on the number of columns?  If you could do that, you wouldn't need dynamic SQL and your problem would get a lot simpler.

    John

  • John Mitchell-245523 - Friday, February 2, 2018 9:24 AM

    You're not imposing.  It's just that it's easier to assist someone who wants help with a specific issue, rather than asking for an end-to-end solution.  You should try working through that article I linked to - you may find it's closer to your situation than you imagine.  And if it isn't, then please do ask for help.

    By the way, might it be possible to put an upper limit on the number of columns?  If you could do that, you wouldn't need dynamic SQL and your problem would get a lot simpler.

    John

    I found that link during my search but, still couldn't get it. I tried putting an upper limit on the number of columns but, the user insists that they need all of them.

  • NineIron,
    Are you doing the summary for this stuff in Access or where? I'm only asking because doing this in SSRS is stupid easy (use a Matrix)
    Pieter

  • pietlinden - Friday, February 2, 2018 10:05 AM

    NineIron,
    Are you doing the summary for this stuff in Access or where? I'm only asking because doing this in SSRS is stupid easy (use a Matrix)
    Pieter

    The results are going to be sftp'd to an outside vendor.

  • Oh, so are you doing that through SSIS?

  • The expected results that you provided do not match the data that you provided.

    From the sample data, how do we know that 'a1117' and 'a1120' both map to 'a1111'?
    It appears that you crafted this data in EXCEL.

    Assuming that the a???? are all a1111, and the b???? are all the b2222, etc, then the following will work
    DECLARE @sql NVARCHAR(MAX); -- Decalre a variable to hold the Dynamic SQL to be executed
    DECLARE @columns NVARCHAR(MAX); -- Declare a vriable to hold the list of columns - [Diag1], [Diag2], etc

    SET @columns = N''; -- Initialise the @columns variable. This is required for the next statement to be able to work. Else it will be NULL

    WITH cteDiagUrn AS (
    -- This cte returns a unique list of DiagnosisUrnID to convert to Fields - , p.[Diag1], p.[Diag2] ...
    SELECT t.DiagnosisUrnID
    FROM #T as t
    GROUP BY t.DiagnosisUrnID
    )
    -- Turn the results from the cte into a CSV list of fields
    SELECT @columns += N', p.' + QUOTENAME('Diag'+convert(varchar(10), cte.DiagnosisUrnID))
    FROM cteDiagUrn AS cte
    ORDER BY cte.DiagnosisUrnID;

    SET @columns = STUFF(@columns, 1, 2, ''); -- Remove the extra ", " at the from of the list

    --Create DSQL to execute
    SET @sql = N'
    SELECT p.account_num, ' + @columns + '
    FROM
    (
     SELECT t.account_num, t.Diagnosis
     , DiagnosisUrnID = ''Diag''+convert(varchar(10), t.DiagnosisUrnID) -- Need to add "Diag" to the front of each DiagnosisUrnID
     FROM #T as t
    ) AS j
    PIVOT
    (
    MAX(Diagnosis) FOR DiagnosisUrnID IN ('
    + REPLACE(@columns, 'p.[', '[')
    + ')
    ) AS p;';

    PRINT @sql; -- Print the DSQL for debugging.
    EXEC sp_executesql @sql; -- EXEC the DSQL.

  • pietlinden - Friday, February 2, 2018 10:33 AM

    Oh, so are you doing that through SSIS?

    No. Just schedule the job to run and use bulk copy to a folder.

  • DesNorton - Friday, February 2, 2018 11:10 AM

    The expected results that you provided do not match the data that you provided.

    From the sample data, how do we know that 'a1117' and 'a1120' both map to 'a1111'?
    It appears that you crafted this data in EXCEL.

    Assuming that the a???? are all a1111, and the b???? are all the b2222, etc, then the following will work
    DECLARE @sql NVARCHAR(MAX); -- Decalre a variable to hold the Dynamic SQL to be executed
    DECLARE @columns NVARCHAR(MAX); -- Declare a vriable to hold the list of columns - [Diag1], [Diag2], etc

    SET @columns = N''; -- Initialise the @columns variable. This is required for the next statement to be able to work. Else it will be NULL

    WITH cteDiagUrn AS (
    -- This cte returns a unique list of DiagnosisUrnID to convert to Fields - , p.[Diag1], p.[Diag2] ...
    SELECT t.DiagnosisUrnID
    FROM #T as t
    GROUP BY t.DiagnosisUrnID
    )
    -- Turn the results from the cte into a CSV list of fields
    SELECT @columns += N', p.' + QUOTENAME('Diag'+convert(varchar(10), cte.DiagnosisUrnID))
    FROM cteDiagUrn AS cte
    ORDER BY cte.DiagnosisUrnID;

    SET @columns = STUFF(@columns, 1, 2, ''); -- Remove the extra ", " at the from of the list

    --Create DSQL to execute
    SET @sql = N'
    SELECT p.account_num, ' + @columns + '
    FROM
    (
     SELECT t.account_num, t.Diagnosis
     , DiagnosisUrnID = ''Diag''+convert(varchar(10), t.DiagnosisUrnID) -- Need to add "Diag" to the front of each DiagnosisUrnID
     FROM #T as t
    ) AS j
    PIVOT
    (
    MAX(Diagnosis) FOR DiagnosisUrnID IN ('
    + REPLACE(@columns, 'p.[', '[')
    + ')
    ) AS p;';

    PRINT @sql; -- Print the DSQL for debugging.
    EXEC sp_executesql @sql; -- EXEC the DSQL.

    Sorry about that.

  • NineIron - Friday, February 2, 2018 11:12 AM

    DesNorton - Friday, February 2, 2018 11:10 AM

    The expected results that you provided do not match the data that you provided.

    From the sample data, how do we know that 'a1117' and 'a1120' both map to 'a1111'?
    It appears that you crafted this data in EXCEL.

    Assuming that the a???? are all a1111, and the b???? are all the b2222, etc, then the following will work
    DECLARE @sql NVARCHAR(MAX); -- Decalre a variable to hold the Dynamic SQL to be executed
    DECLARE @columns NVARCHAR(MAX); -- Declare a vriable to hold the list of columns - [Diag1], [Diag2], etc

    SET @columns = N''; -- Initialise the @columns variable. This is required for the next statement to be able to work. Else it will be NULL

    WITH cteDiagUrn AS (
    -- This cte returns a unique list of DiagnosisUrnID to convert to Fields - , p.[Diag1], p.[Diag2] ...
    SELECT t.DiagnosisUrnID
    FROM #T as t
    GROUP BY t.DiagnosisUrnID
    )
    -- Turn the results from the cte into a CSV list of fields
    SELECT @columns += N', p.' + QUOTENAME('Diag'+convert(varchar(10), cte.DiagnosisUrnID))
    FROM cteDiagUrn AS cte
    ORDER BY cte.DiagnosisUrnID;

    SET @columns = STUFF(@columns, 1, 2, ''); -- Remove the extra ", " at the from of the list

    --Create DSQL to execute
    SET @sql = N'
    SELECT p.account_num, ' + @columns + '
    FROM
    (
     SELECT t.account_num, t.Diagnosis
     , DiagnosisUrnID = ''Diag''+convert(varchar(10), t.DiagnosisUrnID) -- Need to add "Diag" to the front of each DiagnosisUrnID
     FROM #T as t
    ) AS j
    PIVOT
    (
    MAX(Diagnosis) FOR DiagnosisUrnID IN ('
    + REPLACE(@columns, 'p.[', '[')
    + ')
    ) AS p;';

    PRINT @sql; -- Print the DSQL for debugging.
    EXEC sp_executesql @sql; -- EXEC the DSQL.

    Sorry about that.

    This is perfect. I'll spend some time studying it. This is why sqlservercentral.com is such a great forum.

  • John Mitchell-245523 - Friday, February 2, 2018 8:52 AM

    Step by step dynamic pivot script? Your wish is my command.  It's all out there for you if you search for it.

    John

    Not exactly an easy to understand article for a neophyte.  It also uses PIVOT, which is slower than the ancient "Black Arts" method of CROSSTABs.  Please see the following article for a step-by-step article that explains everything for such a dynamic implementation, which is easily capable of other functionality that the PIVOT would have a more difficult time with.

    http://www.sqlservercentral.com/articles/Crosstab/65048/

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 20 total)

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