June 18, 2014 at 8:50 pm
Hello, I have the following data I need to pivot.
CREATE TABLE [dbo].[tmp](
[ID] [int] IDENTITY(1,1) NOT NULL,
[pmDescription] [varchar](75) NULL,
[smDescription] [varchar](40) NULL,
[mstr_list_item_desc] [varchar](100) NULL,
[delete_ind] [char](1) NOT NULL,
[address_line_1] [varchar](55) NULL,
[limit_id] [varchar](10) NULL,
[licType] [varchar](13) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[tmp] ON
INSERT [dbo].[tmp]([ID],[pmDescription],[smDescription],[mstr_list_item_desc],[delete_ind],[address_line_1],[limit_id],[licType]) VALUES('1','Clark MD, Dumisa','Obstetrics/Gyn','OBGYN','N','1221 regal Road','HWPIPROV','HealthWise')
INSERT [dbo].[tmp]([ID],[pmDescription],[smDescription],[mstr_list_item_desc],[delete_ind],[address_line_1],[limit_id],[licType]) VALUES('2','Clark MD, Dumisa','Obstetrics/Gyn','OBGYN','N','1221 regal Road','NEXTMDPROV','PatientPortal')
INSERT [dbo].[tmp]([ID],[pmDescription],[smDescription],[mstr_list_item_desc],[delete_ind],[address_line_1],[limit_id],[licType]) VALUES('3','Clark MD, Dumisa','Obstetrics/Gyn','OBGYN','N','1221 regal Road','RTSPROV','RealTime')
INSERT [dbo].[tmp]([ID],[pmDescription],[smDescription],[mstr_list_item_desc],[delete_ind],[address_line_1],[limit_id],[licType]) VALUES('4','Franklyn MD, Barry','Pediatrics','Pediatrics','N','1221 regal Road','HWPIPROV','HealthWise')
INSERT [dbo].[tmp]([ID],[pmDescription],[smDescription],[mstr_list_item_desc],[delete_ind],[address_line_1],[limit_id],[licType]) VALUES('5','Franklyn MD, Barry','Pediatrics','Pediatrics','N','1221 regal Road','NEXTMDPROV','PatientPortal')
INSERT [dbo].[tmp]([ID],[pmDescription],[smDescription],[mstr_list_item_desc],[delete_ind],[address_line_1],[limit_id],[licType]) VALUES('6','Franklyn MD, Barry','Pediatrics','Pediatrics','N','1221 regal Road','RTSPROV','RealTime')
INSERT [dbo].[tmp]([ID],[pmDescription],[smDescription],[mstr_list_item_desc],[delete_ind],[address_line_1],[limit_id],[licType]) VALUES('7','Selig MD, Alexey','Pulmonary Diseases','Pulmonary/Critical Care','N','1221 regal Road','HWPIPROV','HealthWise')
INSERT [dbo].[tmp]([ID],[pmDescription],[smDescription],[mstr_list_item_desc],[delete_ind],[address_line_1],[limit_id],[licType]) VALUES('8','Selig MD, Alexey','Pulmonary Diseases','Pulmonary/Critical Care','N','1221 regal Road','NEXTMDPROV','PatientPortal')
INSERT [dbo].[tmp]([ID],[pmDescription],[smDescription],[mstr_list_item_desc],[delete_ind],[address_line_1],[limit_id],[licType]) VALUES('9','Selig MD, Alexey','Pulmonary Diseases','Pulmonary/Critical Care','N','1221 regal Road','RTSPROV','RealTime')
SET IDENTITY_INSERT [dbo].[tmp] OFF
I need to have 1 row for each
pmDescription smDescription mstr_list_item_desc delete_ind Address_line_1 licType1 licType2 LicType3
Clark MD, Dumisa Obstetrics/Gyn OBGYN N 1221 regal Road HealthWise PatientPortal RealTime
I hope this all makes sense.
June 18, 2014 at 10:19 pm
Will the lictype always be of these 3 types ?
HealthWise
PatientPortal
RealTime
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
June 18, 2014 at 10:42 pm
Many ways of doing this, here are two quick examples. BTW, thanks for the nice DDL/sample.
😎
Slightly long winded pre 2012.
;WITH LIC_TYPE AS
(
SELECT
TL.ID
,ROW_NUMBER () OVER
(
PARTITION BY TL.pmDescription
,TL.smDescription
,TL.mstr_list_item_desc
ORDER BY TL.ID
) AS LIC_RID
,DENSE_RANK() OVER
(
ORDER BY TL.pmDescription
,TL.smDescription
,TL.mstr_list_item_desc
) AS LIC_LNC
,TL.licType
FROM dbo.tmp TL
)
,LIC_PIV AS
(
SELECT
LT1.LIC_LNC
,LT1.licType AS licType1
,LT2.licType AS licType2
,LT3.licType AS licType3
,ROW_NUMBER() OVER
(
PARTITION BY LT1.LIC_LNC
ORDER BY (SELECT NULL)
) AS LP_RID
FROM LIC_TYPE LT1
OUTER APPLY LIC_TYPE LT2
OUTER APPLY LIC_TYPE LT3
WHERE LT1.LIC_RID = 1
AND LT2.LIC_RID = 2
AND LT3.LIC_LNC = 3
GROUP BY LT1.LIC_LNC
,LT1.licType
,LT2.licType
,LT3.licType
)
,REPORT_BASE AS
(
SELECT
TM.ID
,ROW_NUMBER () OVER
(
PARTITION BY TM.pmDescription
,TM.smDescription
,TM.mstr_list_item_desc
ORDER BY TM.ID
) AS TM_RID
,DENSE_RANK() OVER
(
ORDER BY TM.pmDescription
,TM.smDescription
,TM.mstr_list_item_desc
) AS LIC_LNC
,TM.pmDescription
,TM.smDescription
,TM.mstr_list_item_desc
,TM.delete_ind
,TM.address_line_1
FROM dbo.tmp TM
)
SELECT
RB.ID
,RB.pmDescription
,RB.smDescription
,RB.mstr_list_item_desc
,RB.delete_ind
,RB.address_line_1
,LP.licType1
,LP.licType2
,LP.licType3
FROM REPORT_BASE RB
OUTER APPLY LIC_PIV LP
WHERE RB.LIC_LNC = LP.LIC_LNC
AND LP.LP_RID = 1
AND RB.TM_RID = 1
2012 and later
;WITH REPORT_BASE AS
(
SELECT
TM.ID
,ROW_NUMBER () OVER
(
PARTITION BY TM.pmDescription
,TM.smDescription
,TM.mstr_list_item_desc
ORDER BY TM.ID
) AS TM_RID
,DENSE_RANK() OVER
(
ORDER BY TM.pmDescription
,TM.smDescription
,TM.mstr_list_item_desc
) AS LIC_LNC
,TM.pmDescription
,TM.smDescription
,TM.mstr_list_item_desc
,TM.delete_ind
,TM.address_line_1
,TM.licType
FROM dbo.tmp TM
)
,REPORT_LIC AS
(
SELECT
RB.ID
,ROW_NUMBER() OVER
(
PARTITION BY RB.LIC_LNC
ORDER BY (SELECT NULL)
) AS DD_RID
,RB.pmDescription
,RB.smDescription
,RB.mstr_list_item_desc
,RB.delete_ind
,RB.address_line_1
,RB.licType
,LEAD(RB.licType,1) OVER
(
PARTITION BY RB.LIC_LNC
ORDER BY RB.TM_RID
) AS licType2
,LEAD(RB.licType,2) OVER
(
PARTITION BY RB.LIC_LNC
ORDER BY RB.TM_RID
) AS licType3
FROM REPORT_BASE RB
)
SELECT
RL.ID
,RL.pmDescription
,RL.smDescription
,RL.mstr_list_item_desc
,RL.delete_ind
,RL.address_line_1
,RL.licType
,RL.licType2
,RL.licType3
FROM REPORT_LIC RL
WHERE RL.DD_RID = 1
Results
ID pmDescription smDescription mstr_list_item_desc delete_ind address_line_1 licType licType2 licType3
--- ------------------- ------------------- ------------------------ ---------- ---------------- ------------- ------------- ---------
1 Clark MD, Dumisa Obstetrics/Gyn OBGYN N 1221 regal Road HealthWise PatientPortal RealTime
4 Franklyn MD, Barry Pediatrics Pediatrics N 1221 regal Road HealthWise PatientPortal RealTime
7 Selig MD, Alexey Pulmonary Diseases Pulmonary/Critical Care N 1221 regal Road HealthWise PatientPortal RealTime
June 19, 2014 at 12:19 am
If the lictype's are known in advance you can simply use this
SELECT pmdescription,
smdescription,
mstr_list_item_desc,
delete_ind,
address_line_1,
Max(CASE
WHEN lictype = 'HealthWise' THEN 'HealthWise'
END)licType1,
Max(CASE
WHEN lictype = 'PatientPortal' THEN 'PatientPortal'
END)licType2,
Max(CASE
WHEN lictype = 'RealTime' THEN 'RealTime'
END)licType3
FROM tmp
GROUP BY pmdescription,
smdescription,
mstr_list_item_desc,
delete_ind,
address_line_1
If they are going to be dynamic you can use this
DECLARE @sql VARCHAR(max)='',@columnnames VARCHAR(max)='';
WITH cte
AS (SELECT lictype,
'lictype_' + CONVERT(VARCHAR(100), Row_number()OVER(ORDER BY (SELECT 1))) LID
FROM tmp
GROUP BY lictype)
SELECT @columnnames = @columnnames + ',' + Quotename(lid)
FROM cte
SET @sql = 'SELECT * FROM (';
SET @sql = @sql + 'SELECT pmDescription,smDescription,mstr_list_item_desc,delete_ind,address_line_1,''lictype_'' + convert(varchar(100), ROW_NUMBER()over(partition by pmDescription order by pmDescription)) MM,lictype '
SET @sql = @sql + 'FROM tmp M) as SourceTable ';
SET @sql = @sql + 'PIVOT (max(lictype) FOR MM IN ('
+ Stuff(@columnnames, 1, 1, '')
+ ')) AS PivotTable';
EXEC (@sql)
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
June 19, 2014 at 2:50 am
Sorry, I should have stated that they will not always be the same. Thank you.
June 19, 2014 at 3:05 am
oradbguru (6/19/2014)
Sorry, I should have stated that they will not always be the same. Thank you.
In that case you can use the second query I had posted.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
June 19, 2014 at 6:33 am
Sachin, thank you for you solution. I am modifying it to use joins from several tables instead of a single table as in the initial illustration. Here is what I have, but it throws the following error.
DECLARE @sql VARCHAR(max)='',@columnnames VARCHAR(max)='';
WITH cte
AS (SELECT lictype,
'lictype_' + CONVERT(VARCHAR(100), Row_number()OVER(ORDER BY (SELECT 1))) LID
FROM tmp
GROUP BY lictype)
SELECT @columnnames = @columnnames + ',' + Quotename(lid)
FROM cte
SET @sql = 'SELECT * FROM (';
SET @sql = @sql + 'SELECT pmDescription,smDescription,mstr_list_item_desc,delete_ind,address_line_1,''lictype_'' + convert(varchar(100), ROW_NUMBER()over(partition by pmDescription order by pmDescription)) MM,lictype '
--SET @sql = @sql + 'FROM #tmp M) as SourceTable ';
SET @sql = @sql + 'FROM provider_mstr(NOLOCK) pm INNER JOIN provider_practice_mstr ppm (NOLOCK) ON pm.provider_id = ppm.provider_id '
SET @sql = @sql + 'LEFT OUTER JOIN license_detail ld (NOLOCK) ON CONVERT(VARCHAR(36), pm.provider_id) = ld.limit_value '
SET @sql = @sql + 'LEFT OUTER JOIN specialty_mstr sm (NOLOCK) ON ppm.specialty_code = sm.specialty_code '
SET @sql = @sql + 'LEFT OUTER JOIN mstr_lists ml (NOLOCK) ON pm.provider_subgrouping1_id = ml.mstr_list_item_id '
SET @sql = @sql + 'WHERE ld.limit_id not in (''RTSPROV'', ''EMRUSER'', ''EPMUSER'', ''NEXTMDPROV'', ''HWPIPROV'')) as SourceTable ';
SET @sql = @sql + 'PIVOT (max(lictype) FOR MM IN ('
+ Stuff(@columnnames, 1, 1, '')
+ ')) AS PivotTable';
EXEC (@sql)
Msg 207, Level 16, State 1, Line 1
Invalid column name 'pmDescription'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'smDescription'.
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'delete_ind'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'pmDescription'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'pmDescription'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'lictype'.
Can it be done like this?
Thank you!
June 19, 2014 at 6:51 am
Replace EXEC(@sql) with PRINT(@sql) and check the output of the print statement.Try running the printed sql in the query window.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
June 19, 2014 at 6:54 am
Msg 207, Level 16, State 1, Line 1
Invalid column name 'pmDescription'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'smDescription'.
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'delete_ind'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'pmDescription'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'pmDescription'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'lictype'.
This means when you involve your actual tables, then you need to get the columns alone with their appropriate alias
SELECT pmDescription,smDescription, mlmstr_list_item_desc,delete_ind
like this
SELECT pm.Description,sm.Description, ml.mstr_list_item_desc,delete_ind
June 19, 2014 at 6:54 am
Do these columns exist in any of the referred tables of the query ?
Msg 207, Level 16, State 1, Line 1
Invalid column name 'pmDescription'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'smDescription'.
Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'delete_ind'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'pmDescription'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'pmDescription'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'lictype'.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
June 19, 2014 at 9:15 am
I had the columns named incorrectly. Now I get me result set back, but I need to change limit_id_1 ... limit_id_9 to another value based on a case statement. Here is the code I have.
DECLARE @sql VARCHAR(max)='',@columnnames VARCHAR(max)='';
WITH cte
AS (SELECT limit_id,
'limit_id_' + CONVERT(VARCHAR(100), Row_number()OVER(ORDER BY (SELECT 1))) LID
FROM license_detail
GROUP BY limit_id)
SELECT @columnnames = @columnnames + ',' + Quotename(lid)
FROM cte
SET @sql = 'SELECT * FROM (';
SET @sql = @sql + 'SELECT pm.Description as pmDesc,sm.Description as smDesc,ml.mstr_list_item_desc,pm.delete_ind,pm.address_line_1,''limit_id_'' + convert(varchar(100), ROW_NUMBER()over(partition by pm.Description order by pm.Description)) MM,limit_id '
SET @sql = @sql + 'FROM provider_mstr(NOLOCK) pm INNER JOIN provider_practice_mstr ppm (NOLOCK) ON pm.provider_id = ppm.provider_id '
SET @sql = @sql + 'LEFT OUTER JOIN license_detail ld (NOLOCK) ON CONVERT(VARCHAR(36), pm.provider_id) = ld.limit_value '
SET @sql = @sql + 'LEFT OUTER JOIN specialty_mstr sm (NOLOCK) ON ppm.specialty_code = sm.specialty_code '
SET @sql = @sql + 'LEFT OUTER JOIN mstr_lists ml (NOLOCK) ON pm.provider_subgrouping1_id = ml.mstr_list_item_id '
SET @sql = @sql + 'WHERE ld.limit_id not in (''RTSPROV'', ''EMRUSER'', ''EPMUSER'', ''NEXTMDPROV'', ''HWPIPROV'')) as SourceTable ';
SET @sql = @sql + 'PIVOT (max(sourceTable.limit_id) FOR MM IN ('
+ Stuff(@columnnames, 1, 1, '')
+ ')) AS PivotTable';
EXEC (@sql)
I need to incorporate a case statement like this.
CASE ld.limit_id
WHEN 'RTSPROV' THEN 'RealTime'
WHEN 'NEXTMDPROV' THEN 'PatientPortal'
WHEN 'HWPIPROV' THEN 'HealthWise'
WHEN 'EMRPROV' THEN 'EHR'
WHEN 'EPMPROV' THEN 'EPM'
WHEN 'RTFFMPROV' THEN 'RTF File Mon'
ELSE ld.limit_id
END AS licenseType
Thank you!
June 19, 2014 at 9:32 am
Instead of mixing everything in dynamic sql I would suggest put the desired records into a temporary table and then use only that temporary table in dynamic sql for pivoting.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
June 19, 2014 at 9:37 am
I have considered that. I will try it. Thank you!
June 19, 2014 at 9:46 am
Sachin, this worked very nicely. Thank you for this great solution.
David
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply