Query help with Survey Output - 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 2000 Forums

Transact-SQL (2000)

Query help with Survey Output

Author

Topic

NickyJ
Starting Member

46 Posts

Posted - 2008-07-17 : 04:33:56

I have a survey with answers, each question is phrased so as it relates to a style and there are 3 styles of AR, SI and VV.

Raw data as follows (there are 44 questions but below listed 6 results)

Name,1,2,3,4,5,6
test 1,(a) have a go and see what happens,(b) creative,(a) an image,(a) grasp details of a subject but be unclear about its structure,(a) discuss it,(a) that includes fact and real example
test2,(b) think about what I?m doing first,(b) creative,(a) an image,(a) grasp details of a subject but be unclear about its structure,(a) discuss it,(a) that includes fact and real example
test3,(b) think about what I?m doing first,(b) creative,(b) words,(b) grasp the structure but be unclear about the details,(a) discuss it,(a) that includes fact and real example

I've grouped the answers so as I have the totals for each style using the query below, an answer of B = 1 A = -1.

select Name, Sum(case when substring([1],2,1) = 'b' Then 1 else -1 end
+ case when substring([5],2,1) = 'b' Then 1 else -1 end
+ case when substring([9],2,1) = 'b' Then 1 else -1 end
+ case when substring([13],2,1) = 'b' Then 1 else -1 end
+ case when substring([17],2,1) = 'b' Then 1 else -1 end
+ case when substring([21],2,1) = 'b' Then 1 else -1 end
+ case when substring([25],2,1) = 'b' Then 1 else -1 end
+ case when substring([29],2,1) = 'b' Then 1 else -1 end
+ case when substring([33],2,1) = 'b' Then 1 else -1 end
+ case when substring([37],2,1) = 'b' Then 1 else -1 end
+ case when substring([41],2,1) = 'b' Then 1 else -1 end
) as [AR],

Sum(case when substring([2],2,1) = 'b' Then 1 else -1 end
+ case when substring([6],2,1) = 'b' Then 1 else -1 end
+ case when substring([10],2,1) = 'b' Then 1 else -1 end
+ case when substring([14],2,1) = 'b' Then 1 else -1 end
+ case when substring([18],2,1) = 'b' Then 1 else -1 end
+ case when substring([22],2,1) = 'b' Then 1 else -1 end
+ case when substring([26],2,1) = 'b' Then 1 else -1 end
+ case when substring([30],2,1) = 'b' Then 1 else -1 end
+ case when substring([34],2,1) = 'b' Then 1 else -1 end
+ case when substring([38],2,1) = 'b' Then 1 else -1 end
+ case when substring([42],2,1) = 'b' Then 1 else -1 end
) as [SI],

Sum(case when substring([3],2,1) = 'b' Then 1 else -1 end
+ case when substring([7],2,1) = 'b' Then 1 else -1 end
+ case when substring([11],2,1) = 'b' Then 1 else -1 end
+ case when substring([15],2,1) = 'b' Then 1 else -1 end
+ case when substring([19],2,1) = 'b' Then 1 else -1 end
+ case when substring([23],2,1) = 'b' Then 1 else -1 end
+ case when substring([27],2,1) = 'b' Then 1 else -1 end
+ case when substring([31],2,1) = 'b' Then 1 else -1 end
+ case when substring([35],2,1) = 'b' Then 1 else -1 end
+ case when substring([39],2,1) = 'b' Then 1 else -1 end
+ case when substring([43],2,1) = 'b' Then 1 else -1 end
) as [VV]
from tblData
where Name like 'Test%'
group by Name

This gives me

Name, AR, SI, VV
test 1, -1, -3, -5
test2, 1, 1, -3
test3, -3, -3, 1

Now what I need is a column which tells me which style the respondent mostly reflects so for above I'd like results as follows

Name, AR, SI, VV OutCome
test 1, -1, -3, -5, AR
test2, 1, 1, -3, ARSI
test3, -3, -3, 1, VV

I'd like to return it all in one recordset but I am not sure how to output the column header

any help greatly appreciated.

Thanks

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-07-17 : 04:59:33

[code]
select [Name], AR,SI,VV,
case
when AR < SI and AR < VV then 'AR'
when AR = SI and AR < VV then 'ARSI'
when AR = VV and AR < SI then 'ASVV'
when SI < AR and SI < VV then 'SI'
when SI < AR and SI = VV then 'SIVV'
when VV < AR and VV < SI then 'VV'
end as Outcome
from
(select
[Name],
Sum(case when substring([1],2,1) = 'b' Then 1 else -1 end
+ case when substring([5],2,1) = 'b' Then 1 else -1 end
+ case when substring([9],2,1) = 'b' Then 1 else -1 end
+ case when substring([13],2,1) = 'b' Then 1 else -1 end
+ case when substring([17],2,1) = 'b' Then 1 else -1 end
+ case when substring([21],2,1) = 'b' Then 1 else -1 end
+ case when substring([25],2,1) = 'b' Then 1 else -1 end
+ case when substring([29],2,1) = 'b' Then 1 else -1 end
+ case when substring([33],2,1) = 'b' Then 1 else -1 end
+ case when substring([37],2,1) = 'b' Then 1 else -1 end
+ case when substring([41],2,1) = 'b' Then 1 else -1 end
) as [AR],

Sum(case when substring([2],2,1) = 'b' Then 1 else -1 end
+ case when substring([6],2,1) = 'b' Then 1 else -1 end
+ case when substring([10],2,1) = 'b' Then 1 else -1 end
+ case when substring([14],2,1) = 'b' Then 1 else -1 end
+ case when substring([18],2,1) = 'b' Then 1 else -1 end
+ case when substring([22],2,1) = 'b' Then 1 else -1 end
+ case when substring([26],2,1) = 'b' Then 1 else -1 end
+ case when substring([30],2,1) = 'b' Then 1 else -1 end
+ case when substring([34],2,1) = 'b' Then 1 else -1 end
+ case when substring([38],2,1) = 'b' Then 1 else -1 end
+ case when substring([42],2,1) = 'b' Then 1 else -1 end
) as [SI],

Sum(case when substring([3],2,1) = 'b' Then 1 else -1 end
+ case when substring([7],2,1) = 'b' Then 1 else -1 end
+ case when substring([11],2,1) = 'b' Then 1 else -1 end
+ case when substring([15],2,1) = 'b' Then 1 else -1 end
+ case when substring([19],2,1) = 'b' Then 1 else -1 end
+ case when substring([23],2,1) = 'b' Then 1 else -1 end
+ case when substring([27],2,1) = 'b' Then 1 else -1 end
+ case when substring([31],2,1) = 'b' Then 1 else -1 end
+ case when substring([35],2,1) = 'b' Then 1 else -1 end
+ case when substring([39],2,1) = 'b' Then 1 else -1 end
+ case when substring([43],2,1) = 'b' Then 1 else -1 end
) as [VV]
from tblData
where Name like 'Test%'
group by Name ) a
[/code]

NickyJ
Starting Member

46 Posts

Posted - 2008-07-17 : 11:24:17

Thanks for that Rick as has worked, had to add some more permutations as had another style as below

case
when AR > SI and AR > VV and AR > SG then 'AR'
when AR = SI and AR > VV and AR > SG then 'AR SI'
when AR = VV and AR > SI and AR > SG then 'AR VV'
when AR = SG and AR > SI and AR > VV then 'AR SG'
when AR = SI and AR = VV and AR > SG then 'AR SI VV'
when AR = SI and AR > VV and AR = SG then 'AR SI SG'
when AR > SI and AR = VV and AR = SG then 'AR SG VV'
when AR = SI and AR = VV and AR = SG then 'AR SI VV SG'

when SI > AR and SI > VV and SI > SG then 'SI'
when SI = VV and SI > AR and SI > SG then 'SI VV'
when SI = SG and SI > AR and SI > VV then 'SI SG'
when SI = VV and SI > AR and SI = SG then 'SI VV SG'

when SG > AR and SG > VV and SG > SI then 'SG'
when SG = VV and SG > AR and SG > Si then 'SG VV'

when VV > AR and VV > SI and VV > SG then 'VV'

end as Outcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources