How to use dynamic pivot on this data

Question

How to use dynamic pivot on this data

Viewing 6 posts - 16 through 20 (of 20 total)

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

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: 12537 More actions · Answer 2

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: 12537 More actions · Answer 3

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 · Answer 4

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: 1004406 More actions · Answer 5

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: 1004406 More actions · Answer 6

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)