August 21, 2013 at 2:09 pm
That was so many emails at once I thought someone died.
Er, yeah, sorry about that! SPSS Dimensions makes like 8 tables per project DB. I wouldn't wish this on anyone for free. That's why I started off just asking for some direction to take. It would make me feel like a bad person.
There's not much I can do in the way of re-design. This is the way it sets up everything, tables without end, Amen.
I'm going to skip on the data posting, I don't want you guys to invest any time in this since it's a disaster beyond query re-write. Pour out a little liquor for this one.
August 21, 2013 at 2:11 pm
TheSQLGuru (8/21/2013)
One question: is the numeric value that indicates each "type" of value fixed? If so I think I can come up with something to get what you need in a single seek on that table.
Yeah, everything is fixed. If there's one bright spot, that's it. The rest is all :sick:
August 21, 2013 at 2:16 pm
Let me try to put together a quickie demo to see if what I am thinking will work in this case. I have come across your situation before so I have some experience with it. If what I am thinking of can work it holds the potential to be WAY faster (and more concurrent) unless you are just infrequently pulling one row at a time back.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 21, 2013 at 2:25 pm
TheSQLGuru (8/21/2013)
Let me try to put together a quickie demo to see if what I am thinking will work in this case. I have come across your situation before so I have some experience with it. If what I am thinking of can work it holds the potential to be WAY faster (and more concurrent) unless you are just infrequently pulling one row at a time back.
No, it's an entire chunk of data collection. Be it daily, weekly, monthly, or at the end of the project cycle. But it's 99% likely never going to be one row.
Thank you so much.
August 21, 2013 at 2:56 pm
See if this helps:
CREATE TABLE dbo.#responses (serial tinyint NOT NULL)
CREATE TABLE dbo.#otherdata2 (serial tinyint NOT NULL, variableid tinyint NOT NULL, TextVal varchar(10) NULL)
INSERT dbo.#responses VALUES (1)
INSERT dbo.#responses VALUES (2)
INSERT dbo.#otherdata2 VALUES (1, 1, 'val_1')
INSERT dbo.#otherdata2 VALUES (1, 2, 'val_2')
INSERT dbo.#otherdata2 VALUES (1, 3, 'val_3')
INSERT dbo.#otherdata2 VALUES (1, 5, 'val_5')
INSERT dbo.#otherdata2 VALUES (1, 7, 'val_7')
INSERT dbo.#otherdata2 VALUES (2, 4, 'val_4')
INSERT dbo.#otherdata2 VALUES (2, 6, 'val_6')
SELECT * FROM dbo.#responses
SELECT * FROM dbo.#otherdata2
SELECT r.serial,
val_1.TextVal AS val_1_Stuff,
val_2.TextVal AS val_2_Stuff,
val_3.TextVal AS val_3_Stuff,
val_4.TextVal AS val_4_Stuff
FROM dbo.#responses r
LEFT JOIN (SELECT serial, TextVal
FROM dbo.#otherdata2
WHERE variableid = 1) AS val_1
ON r.serial = val_1.serial
LEFT JOIN (SELECT serial, TextVal
FROM dbo.#otherdata2
WHERE variableid = 2) AS val_2
ON r.serial = val_2.serial
LEFT JOIN (SELECT serial, TextVal
FROM dbo.#otherdata2
WHERE variableid = 3) AS val_3
ON r.serial = val_3.serial
LEFT JOIN (SELECT serial, TextVal
FROM dbo.#otherdata2
WHERE variableid = 4) AS val_4
ON r.serial = val_4.serial
;WITH KGB_Cool_Code AS (
SELECT serial,
MAX(CASE variableid WHEN 1 THEN TextVal END) AS val_1,
MAX(CASE variableid WHEN 2 THEN TextVal END) AS val_2,
MAX(CASE variableid WHEN 3 THEN TextVal END) AS val_3,
MAX(CASE variableid WHEN 4 THEN TextVal END) AS val_4,
MAX(CASE variableid WHEN 5 THEN TextVal END) AS val_5,
MAX(CASE variableid WHEN 6 THEN TextVal END) AS val_6,
MAX(CASE variableid WHEN 7 THEN TextVal END) AS val_7
FROM dbo.#otherData2
GROUP BY serial)
SELECT r.serial,
kgb.val_1 AS val_1_Stuff,
kgb.val_2 AS val_2_Stuff,
kgb.val_3 AS val_3_Stuff,
kgb.val_4 AS val_4_Stuff,
kgb.val_5 AS val_5_Stuff,
kgb.val_6 AS val_6_Stuff,
kgb.val_7 AS val_7_Stuff
FROM dbo.#responses r
INNER JOIN KGB_Cool_Code kgb
ON r.serial = kgb.serial
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 21, 2013 at 3:32 pm
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.
August 22, 2013 at 8:04 am
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'------------
December 3, 2013 at 2:21 pm
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
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply