March 1, 2015 at 4:08 pm
The problem is here:
[Composite Score Eligible ] [nvarchar](255) NULL,
[Composite Score] [nvarchar](255) NULL,
[Composite Score PGS Percentage] [nvarchar](255) NULL,
[coremeasure] [bit] NULL,
[pediatric] [bit] NULL
These 5 fields at the end do not fit the pattern of the others.
Remember we are just pulling columns by numbers, so it is going to try to put [Composite Score Eligible ] and [Composite Score] into one unpivoted pair,
and then [pediatric] and another (non existent) column in another pair.
So not only are we breaking the profile, but pediatric is also a different data type.
Does every table have those 5 columns? Does anything need to be done with those 5 columns? We will need to profile them by name and/or type or something else so that they don't break our dynamic logic.
March 1, 2015 at 5:28 pm
Nevyn (3/1/2015)
The problem is here:[Composite Score Eligible ] [nvarchar](255) NULL,
[Composite Score] [nvarchar](255) NULL,
[Composite Score PGS Percentage] [nvarchar](255) NULL,
[coremeasure] [bit] NULL,
[pediatric] [bit] NULL
These 5 fields at the end do not fit the pattern of the others.
Remember we are just pulling columns by numbers, so it is going to try to put [Composite Score Eligible ] and [Composite Score] into one unpivoted pair,
and then [pediatric] and another (non existent) column in another pair.
So not only are we breaking the profile, but pediatric is also a different data type.
Does every table have those 5 columns? Does anything need to be done with those 5 columns? We will need to profile them by name and/or type or something else so that they don't break our dynamic logic.
The first three are from the original Excel Workbook and, although I may be asked to report on them in the future, for now I don't need them. The last two I added to have a flag to tell me if it's in the pediatric group, the core specialties group, or neither. Allergy and Rheumatology are two specialties that are in neither so they have 0 for both of these flags. All 5 of these columns will be the same always.
Thanks so very much for continuing to help me over days. You're amazing!
March 1, 2015 at 5:40 pm
All 5 of these columns will be the same always
Does this mean that these will be the last five columns in every table always, or just this table always?
March 1, 2015 at 5:51 pm
Every one of these specialty tables always.
March 1, 2015 at 8:24 pm
Ok, so lets add in a condition to ignore the last 5 columns. If these are always the last 5 it should work.
DECLARE @Query nvarchar(max),
@tablename nvarchar(50)=N'Rheumatology'
;
-- First we build a small inline tally table
WITH E(N) AS(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
,cteTally(N) AS( -- In this case, we are having the tally move in increments of 4 starting with an offset. For this example I start at column #8
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))*4+17
FROM E, E x
),getpairs AS ( -- Now we have to figure out the columns that go together in your unpivoted table, and concatenate them
select n,STUFF ((
SELECT ',' + CASE WHEN c.colid = t.N THEN '''' + c.name + ''',' + '['+c.name+']' ELSE '['+c.name+']' END
FROM sys.syscolumns c
JOIN sys.sysobjects o
ON o.id=c.id
WHERE o.xtype='u'
AND o.name=@tablename
AND c.colid IN (t.N,t.N+1)
AND c.colid < (SELECT MAX(colid)-4 FROM sys.syscolumns c2 WHERE c2.id = o.id)
FOR XML PATH('')
),1,1,'') pairs
FROM cteTally t
GROUP BY n
) -- Now we concatenate the groups of pairs into a larger string with the rest of the query
SELECT @query=N'select [uplift specialty], [member po],[practice unit name], [final nomination status]
,[final uplift status], [final rank], [final uplift percentage]
,practiceID=row_number() over (partition by [practice unit name] order by Metricname)
,metricname,Metricvalue, metricpercentilerank
from ' + @tablename + '
cross apply (
values ' + STUFF ((
SELECT N'),(' + pairs
FROM getpairs
WHERE pairs IS NOT NULL
FOR XML PATH ('')),1,2,'')+')) x(metricname,MetricValue, metricpercentilerank)'
EXEC SP_EXECUTESQL @Query
March 1, 2015 at 9:26 pm
This is perfect Nevyn! I can't thank you enough. I tried it with several tables. This is the elegant solution that was eluding me. Cheers.
March 1, 2015 at 9:32 pm
I'm glad it worked for you.
March 19, 2015 at 4:07 pm
Nevyn (3/1/2015)
I'm glad it worked for you.
Unfortunately the boss now says he wants the first three of the four columns that I need to unpivot for each metric. So, instead of just metricname, metricvalue, metricpercentilerank, I now have to get the metricPGS.
The table description follows:
CREATE TABLE [dbo].[Allergy](
[Uplift Specialty] [varchar](150) NOT NULL,
[Member PO] [varchar](150) NOT NULL,
[Member Sub PO] [varchar](150) NOT NULL,
[Practice Unit Name] [varchar](150) NOT NULL,
[Practice Unit ID] [nvarchar](255) NULL,
[Practice Unit Address] [nvarchar](255) NULL,
[Principal Partner PO] [nvarchar](255) NULL,
[Total Specialists] [nvarchar](255) NULL,
[Final Nomination Status] [nvarchar](255) NULL,
[Member PO Nominated ] [nvarchar](255) NULL,
[Member PO Agreement ] [nvarchar](255) NULL,
[Principal Partner PO Nomination Required] [nvarchar](255) NULL,
[Principal Partner PO Nominated] [nvarchar](255) NULL,
[Principal Partner PO Agreement] [nvarchar](255) NULL,
[Final Uplift Status ] [nvarchar](255) NULL,
[Final Uplift Percentage] [nvarchar](255) NULL,
[Final Rank] [int] NULL,
[Number of Ranked Practice Units] [nvarchar](255) NULL,
[Pediatric Members Percentage] [nvarchar](255) NULL,
[Pediatric Flag] [nvarchar](255) NULL,
[PMPM] [nvarchar](255) NULL,
[PMPM Percentile Rank] [nvarchar](255) NULL,
[PMPM PGS] [nvarchar](255) NULL,
[PMPM Rank Number] [nvarchar](255) NULL,
[Cave] [nvarchar](255) NULL,
[Cave Percentile Rank] [nvarchar](255) NULL,
[Cave PGS] [nvarchar](255) NULL,
[Cave Rank Number] [nvarchar](255) NULL,
[Many Allergy Tests] [nvarchar](255) NULL,
[Many Allergy Tests Percentile Rank] [nvarchar](255) NULL,
[Many Allergy Tests PGS] [nvarchar](255) NULL,
[Many Allergy Tests Rank Number] [nvarchar](255) NULL,
[Asthma Spirometry] [nvarchar](255) NULL,
[Asthma Spirometry Percentile Rank] [nvarchar](255) NULL,
[Asthma Spirometry PGS] [nvarchar](255) NULL,
[Asthma Spirometry Rank Number] [nvarchar](255) NULL,
[Allergy Asthma IP ED] [nvarchar](255) NULL,
[Allergy Asthma IP ED Percentile Rank] [nvarchar](255) NULL,
[Allergy Asthma IP ED PGS] [nvarchar](255) NULL,
[Allergy Asthma IP ED Rank Number] [nvarchar](255) NULL,
[Asthma Medication Ratio] [nvarchar](255) NULL,
[Asthma Medication Ratio Percentile Rank] [nvarchar](255) NULL,
[Asthma Medication Ratio PGS] [nvarchar](255) NULL,
[Asthma Medication Ratio Rank Number] [nvarchar](255) NULL,
[Allergy IP ED Followup] [nvarchar](255) NULL,
[Allergy IP ED Followup Percentile Rank] [nvarchar](255) NULL,
[Allergy IP ED Followup PGS] [nvarchar](255) NULL,
[Allergy IP ED Followup Rank Number] [nvarchar](255) NULL,
[Immunotherapy Routine Care] [nvarchar](255) NULL,
[Immunotherapy Routine Care Percentile Rank] [nvarchar](255) NULL,
[Immunotherapy Routine Care PGS] [nvarchar](255) NULL,
[Immunotherapy Routine Care Rank Number] [nvarchar](255) NULL,
[Immunotherapy cost PUMPM] [nvarchar](255) NULL,
[Immunotherapy cost PUMPM Percentile Rank] [nvarchar](255) NULL,
[Immunotherapy cost PUMPM PGS] [nvarchar](255) NULL,
[Immunotherapy cost PUMPM Rank Number] [nvarchar](255) NULL,
[Proportion Intradermal Tests] [nvarchar](255) NULL,
[Proportion Intradermal Tests Percentile Rank] [nvarchar](255) NULL,
[Proportion Intradermal Tests PGS] [nvarchar](255) NULL,
[Proportion Intradermal Tests Rank Number] [nvarchar](255) NULL,
[Composite Score Eligible ] [nvarchar](255) NULL,
[Composite Score] [nvarchar](255) NULL,
[Composite Score PGS Percentage] [nvarchar](255) NULL,
[coremeasure] [bit] NULL,
[pediatric] [bit] NULL,
CONSTRAINT [PK_Allergy] PRIMARY KEY CLUSTERED
(
[Uplift Specialty] ASC,
[Member PO] ASC,
[Member Sub PO] ASC,
[Practice Unit Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
So, starting with the first groups of four each (example here):
[PMPM] [nvarchar](255) NULL,
[PMPM Percentile Rank] [nvarchar](255) NULL,
[PMPM PGS] [nvarchar](255) NULL,
[PMPM Rank Number] [nvarchar](255) NULL,
[Cave] [nvarchar](255) NULL,
[Cave Percentile Rank] [nvarchar](255) NULL,
[Cave PGS] [nvarchar](255) NULL,
[Cave Rank Number] [nvarchar](255) NULL,
[Many Allergy Tests] [nvarchar](255) NULL,
Instead of just needing the first two of every four, I now need the first three. So [PMPM, PMPM Percentile Rank], and [PMPM PGS]. The brilliant code that Nevyn wrote for me below:
declare @query nvarchar(max);
-- build small inline tally table
WITH E(N) AS(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
,cteTally(N) AS( -- In this case, we are having the tally move in increments of 4 starting with an offset. For this example I start at column #21
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))*4+17
FROM E, E x
),getpairs AS ( -- Now we have to figure out the columns that go together in the unpivoted table, and concatenate them
select n,STUFF ((
SELECT ',' + CASE WHEN c.colid = t.N THEN '''' + c.name + ''',' + '['+c.name+']' ELSE '['+c.name+']' END
FROM sys.syscolumns c
JOIN sys.sysobjects o
ON o.id=c.id
WHERE o.xtype='u'
AND o.name=N'ObstetricsGynecology'
AND c.colid IN (t.N,t.N+1)
AND c.colid < (SELECT MAX(colid)-4 FROM sys.syscolumns c2 WHERE c2.id = o.id)
FOR XML PATH('')
),1,1,'') pairs
FROM cteTally t
GROUP BY n
) -- Now we concatenate the groups of pairs into a larger string with the rest of the query
SELECT @query=N'select [uplift specialty], [member po],[practice unit name], [final nomination status]
,[final uplift status], [final rank], [final uplift percentage]
,practiceID=row_number() over (partition by [practice unit name] order by Metricname)
,metricname,Metricvalue, metricpercentilerank
into aaaaaaObstetricsGynecologyUnpivotednonp
from ObstetricsGynecology
cross apply (
values ' + STUFF ((
SELECT N'),(' + pairs
FROM getpairs
WHERE pairs IS NOT NULL
FOR XML PATH ('')),1,2,'')+')) x(metricname,MetricValue, metricpercentilerank)'
EXEC SP_EXECUTESQL @Query
gets the first two. I thought I would be able to modify the code to do what I need now but, after trying a good part of today, I'm afraid I cannot figure out how to do it.
If it would be easier, I could get each of the four in a group instead of three (in case the boss asks for that next).
Should I post this as a new question? I thought that, as this is rather complex, I would add to the existing thread.
Thanks in advance for reading and potentially helping.
March 30, 2015 at 10:20 am
Sorry for the delay, I've been off on vacation for a couple weeks
Try the following:
eclare @query nvarchar(max);
-- build small inline tally table
WITH E(N) AS(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
,cteTally(N) AS( -- In this case, we are having the tally move in increments of 4 starting with an offset. For this example I start at column #21
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))*4+17
FROM E, E x
),getpairs AS ( -- Now we have to figure out the columns that go together in the unpivoted table, and concatenate them
select n,STUFF ((
SELECT ',' + CASE WHEN c.colid = t.N THEN '''' + c.name + ''',' + '['+c.name+']' ELSE '['+c.name+']' END
FROM sys.syscolumns c
JOIN sys.sysobjects o
ON o.id=c.id
WHERE o.xtype='u'
AND o.name=N'ObstetricsGynecology'
AND c.colid IN (t.N,t.N+1,t.N+2)
AND c.colid < (SELECT MAX(colid)-4 FROM sys.syscolumns c2 WHERE c2.id = o.id)
FOR XML PATH('')
),1,1,'') pairs
FROM cteTally t
GROUP BY n
) -- Now we concatenate the groups of pairs into a larger string with the rest of the query
SELECT @query=N'select [uplift specialty], [member po],[practice unit name], [final nomination status]
,[final uplift status], [final rank], [final uplift percentage]
,practiceID=row_number() over (partition by [practice unit name] order by Metricname)
,metricname,Metricvalue, metricpercentilerank,metricGPS
into aaaaaaObstetricsGynecologyUnpivotednonp
from ObstetricsGynecology
cross apply (
values ' + STUFF ((
SELECT N'),(' + pairs
FROM getpairs
WHERE pairs IS NOT NULL
FOR XML PATH ('')),1,2,'')+')) x(metricname,MetricValue, metricpercentilerank, metricGPS)'
EXEC SP_EXECUTESQL @Query
The in clause needs to add t.n+2 to get the new columns, you need to create a name for the new column in the cross apply, and then you need to ask for the new column. This ought to work.
March 30, 2015 at 7:20 pm
Thank you very much Nevyn! No need to apologize at all. I really appreciate your help. I look forward to running your solution on Wednesday (out of the office tomorrow). Again, I am very grateful for your continued replies!
April 2, 2015 at 9:04 am
Thanks Nevyn! I used your solution yesterday and it worked perfectly. I see what you did now. You added the new column that I now needed (the third in the group of 4 related columns) named metricGPS to the line above "into ObstetricsGynecology", added it in the FOR XML PATH statement, and added t.N+2 to the WHERE clause AND c.colid IN (t.N,t.N+1,t.N+2)
If the boss asks for the 4th metric I will use your solution again except with t.N+3...
Thanks, thanks...
Did I say, "Thanks"? If not, THANKS!
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply