I finally got some time to work on this, and wrote a couple dynamic Pivots that get me partially where I need to be. They need a little work, mainly figuring out the best way to join both Pivoted tables to another table, further filtering the dynamic variable lists, and passing a column name filter to the stored procedure.
declare @rcols1 varchar(MAX),
@rcols2 varchar(MAX),
@rquery1 varchar(MAX),
@rquery2 varchar(MAX),
@rquery3 varchar(MAX)
;with repagg1(vpath) as (
select distinct r.variableid
from Responses2 r join Variables v
on r.VariableID = v.VariableID
and v.Type <> 3
)
SELECT @rcols2 = STUFF(
(SELECT distinct ',' + QUOTENAME(cast(c.vpath as varchar(20)))
FROM repagg1 c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
print (@rcols2)
;with repagg2(vpath) as (
select distinct quotename(r.variableid) + ' AS ' + quotename(v.variablename)
from Responses2 r join Variables v
on r.VariableID = v.VariableID
and v.Type <> 3
)
SELECT @rcols1 = STUFF(
(SELECT distinct ',' + c.vpath
FROM repagg2 c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SELECT @rquery1 = 'SELECT serial, ' + @rcols1
SELECT @rquery2= char(10) + ' from
(
select serial
, textval
, variableid
from otherdata2
) x
pivot
(
max(textval)
for variableid in ('
SELECT @rquery3 = char(10) + @rcols2 + char(10) + ') ' + char(10) + ') p'
print(@rquery1 + @rquery2 + @rquery3)
exec (@rquery1 + @rquery2 + @rquery3)
declare @ocols1 varchar(MAX),
@ocols2 varchar(MAX),
@oquery1 varchar(MAX),
@oquery2 varchar(MAX),
@oquery3 varchar(MAX)
;with repagg1(vpath) as (
select distinct r.variableid
from OtherData2 r join Variables v
on r.VariableID = v.VariableID
and v.Type = 2
)
SELECT @ocols2 = STUFF(
(SELECT distinct ',' + QUOTENAME(cast(c.vpath as varchar(20)))
FROM repagg1 c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
print (@ocols2)
;with repagg2(vpath) as (
select distinct quotename(r.variableid) + ' AS ' + quotename(v.variablename)
from OtherData2 r join Variables v
on r.VariableID = v.VariableID
and v.Type = 2
)
SELECT @ocols1 = STUFF(
(SELECT distinct ',' + c.vpath
FROM repagg2 c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SELECT @oquery1 = 'SELECT serial, ' + @ocols1
SELECT @oquery2= char(10) + ' from
(
select serial
, textval
, variableid
from otherdata2
) x
pivot
(
max(textval)
for variableid in ('
SELECT @oquery3 = char(10) + @ocols2 + char(10) + ') ' + char(10) + ') p'
print(@oquery1 + @oquery2 + @oquery3)
exec (@oquery1 + @oquery2 + @oquery3)
The output ends up like this:
SELECT serial, [1] AS [Respondent.Serial],[10] AS [DataCollection.MetadataVersionNumber],[100] AS [sample_ORC_InterviewerGroup],[101] AS [sample_ORC_IntReview],[102] AS [sample_NAME2],[104] AS [sample_CLIENT_POLICY_NUMBER_YEAR],[105] AS [sample_CLIENT_VIEWS_KEY],[106] AS [sample_CLIENT_POL_HOME_STATE],[107] AS [sample_CLIENT_REGN],[108] AS [sample_CLIENT_FIRST_YR],[109] AS [sample_CLIENT_TENURE],[11] AS [DataCollection.MetadataVersionGUID],[110] AS [sample_CLIENT_POL_EFF_DATE],[111] AS [sample_CLIENT_CANCELLATION_EFF_DATE],[112] AS [sample_CLIENT_LAPSETIME],[114] AS [sample_CLIENT_PH1_MID_NAME],[115] AS [sample_CLIENT_PH2_FIRST_NAME],[116] AS [sample_CLIENT_PH2_MID_NAME],[117] AS [sample_CLIENT_PH2_LAST_NAME],[118] AS [sample_CLIENT_CHANNEL_NAME],[119] AS [sample_CLIENT_BL_LIMIT],[120] AS [sample_CLIENT_BILLING_METHOD],[121] AS [sample_CLIENT_ACS],[122] AS [sample_CLIENT_POSU_SCORE],[123] AS [sample_CLIENT_PRIOR_CARRIER_GROUP],[124] AS [sample_CLIENT_INSURANCE_SCORE_DECILE],[125] AS [sample_CLIENT_PRIMARY_DRIVER_AGE],[126] AS [sample_CLIENT_MSC_TIER],[127] AS [sample_CLIENT_CABS],[128] AS [sample_CLIENT_DNC],[130] AS [info_Browser],[131] AS [info_PagesAsked],[132] AS [info_LastAsked],[133] AS [info_LastAskedTime],[134] AS [info_RandomSeed],[135] AS [info_ServerTime],[136] AS [info_Timeouts],[14] AS [DataCollection.EndQuestion],[141] AS [PASSED_SCREENER],[145] AS [InterviewLength],[146] AS [InterviewLengthPostScreener],[148] AS [SurveyTiming.Sections[{Intro}]].Length],[149] AS [SurveyTiming.Sections[{Screen}]].Length],[15] AS [DataCollection.TerminateSignal],[150] AS [SurveyTiming.Sections[{Sec1}]].Length],[151] AS [SurveyTiming.Sections[{Sec2}]].Length],[152] AS [SurveyTiming.Sections[{Sec3}]].Length],[153] AS [SurveyTiming.Sections[{Sec4}]].Length],[154] AS [SurveyTiming.Sections[{Sec5}]].Length],[155] AS [SurveyTiming.Sections[{Sec6}]].Length],[156] AS [SurveyTiming.Sections[{Demog}]].Length],[16] AS [DataCollection.SeedValue],[17] AS [DataCollection.InterviewEngine],[18] AS [DataCollection.CurrentPage],[181] AS [Q2._94],[184] AS [Q3A._94],[185] AS [Q4],[188] AS [Q4B._94],[189] AS [Mrk_Q4b],[191] AS [Q4C._95],[192] AS [Mrk_Q4_1],[194] AS [Q4D],[20] AS [DataCollection.ServerTimeZone],[200] AS [Q5._17],[201] AS [Q5._94],[206] AS [Q5C._94],[208] AS [Q5D._94],[21] AS [DataCollection.InterviewerTimeZone],[211] AS [Q6_2],[213] AS [Q6_3],[215] AS [Q6_4],[217] AS [Q6_7],[22] AS [DataCollection.RespondentTimeZone],[227] AS [Q6E],[246] AS [Q11._94],[247] AS [Q12],[253] AS [Q13B],[27] AS [sample_PRO_PID],[28] AS [sample_PRO_FIELD1],[29] AS [sample_PRO_FIELD2],[293] AS [Q17A],[30] AS [sample_PRO_FIELD3],[31] AS [sample_PRO_FIELD4],[317] AS [Q18A],[319] AS [Q18B],[32] AS [sample_PRO_FIELD5],[324] AS [Q21._94],[325] AS [Q21._95],[326] AS [Q21._96],[327] AS [Mrk_Q22_1],[328] AS [Mrk_Q22_2],[329] AS [Mrk_Q22_3],[33] AS [sample_ORC_SAMPLEPROVIDERID],[331] AS [Q22._91],[334] AS [Mrk_Q22_4],[339] AS [Q23],[342] AS [Q24._94],[345] AS [Q25B],[35] AS [sample_ORC_LANGUAGE],[358] AS [D1],[36] AS [sample_ORC_EMAILVERID],[378] AS [Q25C._94],[42] AS [sample_ORC_SALUTATION],[43] AS [sample_ORC_FNAME],[44] AS [sample_ORC_LNAME],[45] AS [sample_ORC_FULLNAME],[46] AS [sample_ORC_TITLE],[47] AS [sample_ORC_EMAIL],[48] AS [sample_ORC_COMPANY],[49] AS [sample_ORC_ADDRESS],[5] AS [Respondent.ID],[50] AS [sample_ORC_CITY],[51] AS [sample_ORC_STATE],[52] AS [sample_ORC_ZIP],[53] AS [sample_ORC_COUNTRY],[54] AS [sample_ORC_REGION],[55] AS [sample_ORC_PHONE1],[56] AS [sample_ORC_PHONE2],[57] AS [sample_ORC_GENDER],[58] AS [sample_ORC_AGE],[60] AS [sample_ORC_MARITAL],[61] AS [sample_ORC_ETHNICITY],[62] AS [sample_ORC_INCOME],[63] AS [sample_ORC_PASSWORD],[64] AS [sample_ORC_QUOTACELL],[65] AS [sample_ORC_SAMPLEPUNCH],[66] AS [sample_ORC_SEGMENT],[67] AS [sample_ORC_ACTIVATE_DATE],[68] AS [sample_ORC_SHELL_VERSION],[69] AS [sample_ORC_SCRIPT_VERSION],[7] AS [DataCollection.InterviewerID],[70] AS [sample_ORC_WAVE_NUMBER],[71] AS [sample_ORC_JOBID],[72] AS [sample_ORC_REPS],[73] AS [sample_ORC_CELL1],[74] AS [sample_ORC_CELL2],[75] AS [sample_ORC_CELL3],[76] AS [sample_ORC_SAMPLE_FILENAME],[77] AS [sample_ORC_FAX],[78] AS [sample_ORC_FIPS_CODE],[79] AS [sample_ORC_METRO_STATUS],[8] AS [DataCollection.StartTime],[80] AS [sample_ORC_MSA_CODE],[81] AS [sample_ORC_CENSUS_DIVISION],[82] AS [sample_ORC_ADI_CODE_NEILSEN],[83] AS [sample_ORC_ADI_RANK_NEILSEN],[84] AS [sample_ORC_DMA_CODE],[85] AS [sample_ORC_DMA_RANK],[86] AS [sample_ORC_PRIZM_CODE],[87] AS [sample_ORC_METRO_AREA],[88] AS [sample_ORC_LISTED_HH],[89] AS [sample_ORC_TOTAL_HH],[9] AS [DataCollection.FinishTime],[90] AS [sample_ORC_TOTAL_POPULATION],[91] AS [sample_ORC_HOUSEHOLD_DENSITY],[92] AS [sample_ORC_SIC_CODE],[93] AS [sample_ORC_EMPLOYEE_SIZE],[94] AS [sample_ORC_EMPLOYEE_SIZE_CODED],[95] AS [sample_ORC_SALES],[96] AS [sample_ORC_SALES_CODED],[97] AS [sample_ORC_DUNS_ASSIGNMENT],[98] AS [sample_PhoneNumber],[99] AS [sample_InterviewMode]
from
(
select serial
, textval
, variableid
from otherdata2
) x
pivot
(
max(textval)
for variableid in (
[1],[10],[100],[101],[102],[104],[105],[106],[107],[108],[109],[11],[110],[111],[112],[114],[115],[116],[117],[118],[119],[120],[121],[122],[123],[124],[125],[126],[127],[128],[130],[131],[132],[133],[134],[135],[136],[14],[141],[145],[146],[148],[149],[15],[150],[151],[152],[153],[154],[155],[156],[16],[17],[18],[181],[184],[185],[188],[189],[191],[192],[194],[20],[200],[201],[206],[208],[21],[211],[213],[215],[217],[22],[227],[246],[247],[253],[27],[28],[29],[293],[30],[31],[317],[319],[32],[324],[325],[326],[327],[328],[329],[33],[331],[334],[339],[342],[345],[35],[358],[36],[378],[42],[43],[44],[45],[46],[47],[48],[49],[5],[50],[51],[52],[53],[54],[55],[56],[57],[58],[60],[61],[62],[63],[64],[65],[66],[67],[68],[69],[7],[70],[71],[72],[73],[74],[75],[76],[77],[78],[79],[8],[80],[81],[82],[83],[84],[85],[86],[87],[88],[89],[9],[90],[91],[92],[93],[94],[95],[96],[97],[98],[99]
)
) p