February 2, 2018 at 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')
February 2, 2018 at 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
February 2, 2018 at 9:12 am
Thom A - Friday, February 2, 2018 8:51 AMWith 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.
February 2, 2018 at 9:15 am
John Mitchell-245523 - Friday, February 2, 2018 8:52 AMStep 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.
February 2, 2018 at 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
February 2, 2018 at 9:31 am
John Mitchell-245523 - Friday, February 2, 2018 9:24 AMYou'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.
February 2, 2018 at 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
February 2, 2018 at 10:12 am
pietlinden - Friday, February 2, 2018 10:05 AMNineIron,
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.
February 2, 2018 at 10:33 am
Oh, so are you doing that through SSIS?
February 2, 2018 at 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 workDECLARE @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.
February 2, 2018 at 11:10 am
pietlinden - Friday, February 2, 2018 10:33 AMOh, so are you doing that through SSIS?
No. Just schedule the job to run and use bulk copy to a folder.
February 2, 2018 at 11:12 am
DesNorton - Friday, February 2, 2018 11:10 AMThe 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], etcSET @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.
February 2, 2018 at 11:15 am
NineIron - Friday, February 2, 2018 11:12 AMDesNorton - Friday, February 2, 2018 11:10 AMThe 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], etcSET @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.
February 4, 2018 at 3:23 pm
John Mitchell-245523 - Friday, February 2, 2018 8:52 AMStep 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
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply