SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Replacement for a whole mess of left joins?


Replacement for a whole mess of left joins?

Author
Message
sqldriver
sqldriver
SSC Eights!
SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)

Group: General Forum Members
Points: 982 Visits: 2518
Hi Kevin,

That's a really good starting place for me. It doesn't quite put everything on one line when matching on serial (when I run it on actual data), but I think I can work that part out eventually. That mess of a query I posted returns pretty orderly horizontal results.

Thanks again. I know this was a soup sandwich.
David McKinney
David McKinney
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1323 Visits: 2090
This is cosmetic, but all these nested subselects could be tidied up with a few inner joins

Thus we would replace
-----------
and T3689065.serial not in (
select serial from T3689065.dbo.responses2 where
variableid = (select variableid from T3689065.dbo.variables where variablename = 'DataCollection.Status')
and response = (
select cat_dbvalue from utilities.dbo.mdm_categoricals where
id_project = (select id_project from utilities.dbo.orc_projects where project_spss = 'T3689065')
and cat_name = 'Test'
and cat_parentname = 'DataCollection.Status'
)


------

with

----------------

and T3689065.serial not in (
select serial from T3689065.dbo.responses2 r2
inner join T3689065.dbo.variables v
on r2.variableid=v.variableid and v.variablename = 'DataCollection.Status'
inner join utilities.dbo.mdm_categoricals cat
on r2.response=cat.cat_dbvalue and cat.cat_name = 'Test'
and cat.cat_parentname = 'DataCollection.Status'
inner join utilities.dbo.orc_projects proj on r2.id_project=proj.id_project and proj.project_spss = 'T3689065'

------------
sqldriver
sqldriver
SSC Eights!
SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)

Group: General Forum Members
Points: 982 Visits: 2518
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search