Survey Product Split - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Survey Product Split

Author

Topic

sfortner
Yak Posting Veteran

63 Posts

Posted - 2010-09-05 : 23:56:52

Hey Gurus,

I've got an interesting real-life problem I thought I'd check with you on. I've got one table called SurveyAnswers with a SurveyID (int), SurveyQuestion (int) and a answer column (tinyint) with a 1, 2 or 3 in it (SeverityID). Table 2 is named SurveyKey with SurveyQuestion (int) and a comma-delimited list of ProductIDs which is a varchar(50). Table 3 is a Products table with ProductID (int) and Name (varchar(50)). It's a survey which lets a user indicate a particular severity, 1 being mild, 2 moderate and 3 severe. The SurveyAnswers might look like this for a SurveyID of 1 (one particular survey):

1 | 1 | 2
1 | 2 | 1
1 | 3 | 1
1 | 4 | 3

The SurveyKey table might look like this:

1 | 3,5,6
2 | 1,3
3 | 4
4 | 2,7

And the Products table looks like this:

1 | Product1
2 | Product2
etc.

The goal is to determine how many products are associated with the questions based on the severity. If severity is 1, one instance of the product is needed. If severity is 2, two instances of the product is needed, and if severity is 3, three instances. In our example, for question 1 with severity 2 we have

Product3 x 2
Product5 x 2
Product6 x 2

For question 2 we have

Product1 x 1
Product3 x 1

For question 3 we have

Product4 x 1

And for question 4 we have

Product2 x 3
Product7 x 3

To sum it, we'd have

Product1 - 1
Product2 - 3
Product3 - 3
Product4 - 1
Product5 - 2
Product6 - 2
Product7 - 3

I'm using Peso's csv split fcn here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648 (with Kristen's fix) as a UDF. The problem is that I'm having trouble combining the ProductIDs along with the complexity. I thought about an inner derived table which would return a table with just the ProductIDs each in a column by themselves, but multiple rows, but that doesn't seem to work since they are coupled with severity and are also themselves comma-separated and on individual rows within the SurveyKey.

I thought about converting the SurveyKey table to have one SurveyQuestion and ProductID for each row, but that's no fun. I set this up the way I did because I thought it was going to be easy to parse the comma-delimited list and couple that with the severity. It's throwing me - so I'm asking for help! Please, no cursors or while loops - I'd convert the SurveyKey to contain individual ProductID before I'd use that! TIA, Steve

--Steve

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-09-06 : 00:25:22

[code]
-- Sample Table
declare @SurveyAnswers table
(
SurveyID int,
SurveyQuestion int,
Answer int
)

declare @SurveyKey table
(
SurveyQuestion int,
ProductID varchar(10)
)

declare @Products table
(
ProductId int,
[Name] varchar(10)
)

-- Sample Data
insert into @SurveyAnswers
select 1, 1, 2 union all
select 1, 2, 1 union all
select 1, 3, 1 union all
select 1, 4, 3

insert into @SurveyKey
select 1, '3,5,6' union all
select 2, '1,3' union all
select 3, '4' union all
select 4, '2,7'

insert into @Products
select 1, 'Product1' union all
select 2, 'Product2' union all
select 3, 'Product3' union all
select 4, 'Product4' union all
select 5, 'Product5' union all
select 6, 'Product6' union all
select 7, 'Product7'

-- The Query
select p.[Name], Answer = sum(a.Answer)
from @SurveyAnswers a
inner join @SurveyKey k on a.SurveyQuestion = k.SurveyQuestion
cross apply dbo.CSVTable(k.ProductID) csv
inner join @Products p on csv.numberval = p.ProductId
group by p.[Name][/code]

KH
[spoiler]Time is always against us[/spoiler]

sfortner
Yak Posting Veteran

63 Posts

Posted - 2010-09-06 : 21:29:42

Fantastic khtan - that cross apply was just what I needed! This worked like a dream. Thanks again!

--Steve

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources