Dynamically display output based on the parameter

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

General SQL Server Forums

New to SQL Server Programming

Dynamically display output based on the parameter

Author

Topic

Petronas
Posting Yak Master

134 Posts

Posted - 2012-01-18 : 13:37:11

I have a query that pulls all the survey questions and the survey responses for it based on the survey_id.
The issue is that each survey_id could have different number of questions and different number of survey responses.
The ouput required is as below . Below is my exisiting query. NOt sure who do I go about dynamically changing the Q1...Q5 as well as the survey responses.

Thanks in advance,
Petronas

OUTPUT
One Two Three Four Five (survey responses)
(Questions)
Q1
Q2
Q3
Q4

QUERY

Declare @survey_id int
IF OBJECT_ID('Tempdb..#Temp_Survey') IS NOT NULL DROP TABLE #Temp_Survey
SELECT
sd.Survey_Id,
sd.Survey_question,
sd.survey_response
INTO #Temp_Survey
FROM Survey_Detail sd with (nolock)
INNER JOIN survey_response sr with (nolock)
ON sd.survey_id=sr.survey_id
AND sd.track_num=sr.track_num
inner join survey_question sq with (nolock)
on sq.question_id = sr.question_id
WHERE
sd.survey_id =@survey_id

SELECT
Q_Number,
SUM(Total_Cnt) as Total_Cnt,
SUM(Value_One) as Value_One,
SUM(Value_Two) as Value_Two,
SUM(Value_Three) as Value_Three,
SUM(Value_Four) as Value_Four,
SUM(Value_Five) as Value_Five
FROM(
SELECT
'Q1' as Q_Number,
COUNT(*) as Total_Cnt,
SUM(CASE WHEN survey_response = 1 THEN 1 ELSE 0 END) as Value_One,
SUM(CASE WHEN survey_response = 2 THEN 1 ELSE 0 END) as Value_Two,
SUM(CASE WHEN survey_response = 3 THEN 1 ELSE 0 END) as Value_Three,
SUM(CASE WHEN survey_response = 4 THEN 1 ELSE 0 END) as Value_Four,
SUM(CASE WHEN survey_response = 5 THEN 1 ELSE 0 END) as Value_Five
FROM #Temp_Survey with (nolock)
WHERE survey_response BETWEEN 1 AND 5
AND survey_Question ='Q1'

UNION ALL

SELECT
'Q2' as Q_Number,
COUNT(*) as Total_Cnt,
SUM(CASE WHEN survey_response = 1 THEN 1 ELSE 0 END) as Value_One,
SUM(CASE WHEN survey_response = 2 THEN 1 ELSE 0 END) as Value_Two,
SUM(CASE WHEN survey_response = 3 THEN 1 ELSE 0 END) as Value_Three,
SUM(CASE WHEN survey_response = 4 THEN 1 ELSE 0 END) as Value_Four,
SUM(CASE WHEN survey_response = 5 THEN 1 ELSE 0 END) as Value_Five
FROM #Temp_Survey with (nolock)
WHERE survey_response BETWEEN 1 AND 5
AND survey_Question ='Q2'

UNION ALL

SELECT
'Q3' as Q_Number,
COUNT(*) as Total_Cnt,
SUM(CASE WHEN survey_response = 1 THEN 1 ELSE 0 END) as Value_One,
SUM(CASE WHEN survey_response = 2 THEN 1 ELSE 0 END) as Value_Two,
SUM(CASE WHEN survey_response = 3 THEN 1 ELSE 0 END) as Value_Three,
SUM(CASE WHEN survey_response = 4 THEN 1 ELSE 0 END) as Value_Four,
SUM(CASE WHEN survey_response = 5 THEN 1 ELSE 0 END) as Value_Five
FROM #Temp_Survey with (nolock)
WHERE survey_response BETWEEN 1 AND 5
AND survey_Question ='Q3'

UNION ALL

SELECT
'Q4' as Q_Number,
COUNT(*) as Total_Cnt,
SUM(CASE WHEN survey_response = 1 THEN 1 ELSE 0 END) as Value_One,
SUM(CASE WHEN survey_response = 2 THEN 1 ELSE 0 END) as Value_Two,
SUM(CASE WHEN survey_response = 3 THEN 1 ELSE 0 END) as Value_Three,
SUM(CASE WHEN survey_response = 4 THEN 1 ELSE 0 END) as Value_Four,
SUM(CASE WHEN survey_response = 5 THEN 1 ELSE 0 END) as Value_Five
FROM #Temp_Survey with (nolock)
WHERE survey_response BETWEEN 1 AND 5
AND survey_Question ='Q4'
) a

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2012-01-18 : 13:41:28

Hello Petronas,

If possible, can you post some sample base data and 1 or 2 listings of your desired output?

TIA.

Petronas
Posting Yak Master

134 Posts

Posted - 2012-01-18 : 14:15:05

Thanks for your help TIA. Here it is:
The three tables used in the exisiting query are

1. Survey Detail
Survey_ID Survey_Question Track_Num Survey_response
1 1 1123456789 254
1 2 20632287 5
1 3 28974112729 3
1 4 28974112729 4
2 1 29102123873 5
2 2 29102123873 5
2 3 29108544255 4

2. Survey Response
Survey_ID Track_Num unit_ID StartDate EndDate Language Verbatim
1 1123456789 10 1/17/2012 15:06 1/17/2012 15:08 E NULL
1 20632188 6 1/17/2012 15:16 1/17/2012 15:16 E NULL
1 20632287 6 1/17/2012 15:20 1/17/2012 15:21 E NULL

3. Survey_Question
Survey_ID Survey_question response_low response_high Language_Short
1 1 1 5 Clear
1 2 1 5 Experience
1 3 1 5 Confident
1 4 1 5 Satisfied
2 1 1 5 Clear
2 2 1 5 Experience
2 3 1 5 Confident
2 4 1 5 Satisfied

The fourth table (not used in the above query) but contains the total survey questions for each survey_id

Survey
Survey_ID Partner_ID Question_Total
1 12 4
2 12 4

The output

One Two Three Four Five (survey_responses) Total Count
(Survey_questions)
Q1 9 3 0 3 36 51
Q2 6 6 3 6 21 42
Q3 6 6 6 3 12 33
Q4 6 3 3 6 12 30

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2012-01-18 : 15:25:20

Thanks,

Perhaps something like the following would stimulate some thought. Once you have your aggregates, you could simply pivot the data to produce your desired output.

SELECT 
	d.survey_question,
	d.survey_response,
	SUM(CASE WHEN sd.survey_response IS NULL THEN 0 ELSE 1 END) AS ttlResponse
FROM
(
	SELECT
		sq.Survey_ID,
		sq.survey_question,
		p1.number as survey_response
	FROM survey_question sq
		CROSS JOIN master..spt_values p1
	WHERE p1.type = 'p'
		AND p1.number BETWEEN 1 AND sq.response_high
		AND sq.Survey_ID = @survey_id 
) d
LEFT JOIN survey_detail sd ON sd.Survey_ID = d.Survey_ID 
       AND sd.Survey_Question = d.Survey_question 
       AND sd.Survey_response = d.survey_response
GROUP BY d.survey_question, d.survey_response
ORDER BY d.Survey_question, d.survey_response

Petronas
Posting Yak Master

134 Posts

Posted - 2012-01-18 : 16:21:23

Thanks for taking the time to help me with this!!Appreciate it !!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources