How to use dynamic pivot on this data

• Jeff Moden

  SSC Guru

  Points: 1003868

  More actions

  February 4, 2018 at 3:41 pm

  #1978479

  NineIron - Friday, February 2, 2018 8:38 AM

  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')

  Do you have some better test data?  All of your account numbers in your test data are unique and therefor not effectively pivot-able.

  --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)
  

• NineIron

  SSChampion

  Points: 12529

  More actions

  February 5, 2018 at 4:00 am

  #1978517

  Jeff Moden - Sunday, February 4, 2018 3:41 PM

  NineIron - Friday, February 2, 2018 8:38 AM

  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')

  Do you have some better test data?  All of your account numbers in your test data are unique and therefor not effectively pivot-able.

  Jeff,
  I should have looked at the account numbers better. That was the affect of doing a "copy down" in EXCEL. The numbers should be a1111, b2222, c3333, and d4444.

• NineIron

  SSChampion

  Points: 12529

  More actions

  February 5, 2018 at 4:01 am

  #1978518

  Jeff Moden - Sunday, February 4, 2018 3:23 PM

  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/

  Thanx. I'll take a peek.

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  February 5, 2018 at 1:40 pm

  #1978630

  Maybe something like:
  
SET NOCOUNT ON;
DROP TABLE IF EXISTS ##t;
CREATE TABLE ##t
( account_num varchar(10)
, DiagnosisUrnID int
, Diagnosis varchar(10)
);
INSERT INTO ##t
   ( account_num
   , DiagnosisUrnID
   , Diagnosis
   )
VALUES
     ('a1111',1,'Z12.11')
   , ('a1112',2,'K62.1')
   , ('a1113',3,'D12.3')
   , ('a1114',4,'D12.2')
   , ('a1115',5,'K57.30')
   , ('a1116',6,'K64.0')
   , ('a1117',7,'I10')
   , ('a1118',8,'E78.5')
   , ('a1119',9,'E66.9')
   , ('a1120',10,'Z68.30')
   , ('a1121',11,'F32.9')
   , ('b2222',1,'M65.331')
   , ('b2223',2,'E11.40')
   , ('b2224',3,'Z79.84')
   , ('c3333',1,'Z30.432')
   , ('c3334',2,'F17.210')
   , ('d4444',1,'G56.21')
   , ('d4445',2,'G56.01')
   , ('d4446',3,'F17.210')
   , ('d4447',4,'I10')
   , ('d4448',5,'E78.5')
   , ('d4449',6,'E11.42')
   , ('d4450',7,'Z79.84');
DECLARE
       @sql nvarchar(max)
     , @cols nvarchar(max)=N''
     , @jic int=0
     , @comma nchar(1)=N',';

  SELECT
   @cols += @comma + QUOTENAME(CAST(t.DiagnosisUrnID as nvarchar(20))), @jic += t.DiagnosisUrnID
  FROM ##t t
  GROUP BY t.DiagnosisUrnID
  ORDER BY t.DiagnosisUrnID ;
  SET @sql =
  'SELECT account_num'+@cols+'
  FROM ##t t
  PIVOT
   (
   Max(Diagnosis) For t.DiagnosisUrnID IN('+RIGHT(@cols, LEN(@cols)-1)+')
   ) p;';
  EXEC(@sql);
  PRINT(@sql);
  DROP TABLE ##t;
  GO

  

• Jeff Moden

  SSC Guru

  Points: 1003868

  More actions

  February 5, 2018 at 2:16 pm

  #1978634

  NineIron - Monday, February 5, 2018 4:00 AM

  Jeff Moden - Sunday, February 4, 2018 3:41 PM

  NineIron - Friday, February 2, 2018 8:38 AM

  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')

  Do you have some better test data?  All of your account numbers in your test data are unique and therefor not effectively pivot-able.

  Jeff,
  I should have looked at the account numbers better. That was the affect of doing a "copy down" in EXCEL. The numbers should be a1111, b2222, c3333, and d4444.

  Got it.  I'll revisit tonight.

  --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)
  

• Jeff Moden

  SSC Guru

  Points: 1003868

  More actions

  February 5, 2018 at 2:20 pm

  #1978635

  Jeff Moden - Monday, February 5, 2018 2:16 PM

  Got it.  I'll revisit tonight.

  Hmmm...  I might not have to.  Have you seen Joe Torre's code above?  Even though he didn't have the correct data to work with, that's one way to do it with PIVOT.  Does it meet your requirements?

  --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)