May 27, 2014 at 6:16 pm
Hi
I have a large SQL 2012 table containing survey details. The number of questions vary in each survey and can range in number from as little as 10 questions to a maximum of 50.
I need to adapt my crosstab code below to include a CASE statement that outputs a column (Q1, Q2, Q3 etc) representing the questions up to a maximum 50 questions (Q50) and to place the answer under the corresponding question column within each survey. Ideally I want to avoid having to write 50 CASE statements in my code. I chose the CASE statement method as I understand that the PIVOT option isn't as flexible,
I have included some test data and the output should look like this:
SURVEY_REFQ1Q2Q3Q4Q5ETCETC
100 AnswerAnswerAnswerNULLNULL
200 AnswerAnswerAnswerAnswerAnswer
300 AnswerAnswerNULLNULLNULL
--CREATE TEST DATA
IF OBJECT_ID('tempdb..#PivotTestTable') IS NOT NULL DROP TABLE #PivotTestTable
CREATE TABLE #PivotTestTable
(
[SURVEY_REF] [nvarchar](100) NOT NULL
,[QUESTION] [nvarchar](MAX) NULL
,[ANSWER] [nvarchar](100) NULL
,[DATE_REC] [nvarchar](10) NULL
,[TIME_REC] [nvarchar](10) NULL
)
INSERT INTO #PivotTestTable
(
[SURVEY_REF]
,[QUESTION]
,[ANSWER]
,[DATE_REC]
,[TIME_REC]
)
VALUES
('100','1','Answer details recorded for survey ref 100 - Q1','01/01/2014','13:04:02')
,('100','2','Answer details recorded for survey ref 100 - Q2','01/01/2014','13:04:10')
,('100','3','Answer details recorded for survey ref 100 - Q3','01/01/2014','13:04:18')
,('200','1','Answer details recorded for survey ref 200 - Q1','01/02/2014','11:26:28')
,('200','2','Answer details recorded for survey ref 200 - Q2','01/02/2014','11:26:32')
,('200','3','Answer details recorded for survey ref 200 - Q3','01/02/2014','11:26:40')
,('200','4','Answer details recorded for survey ref 200 - Q4','01/02/2014','11:26:44')
,('200','5','Answer details recorded for survey ref 200 - Q5','01/02/2014','11:26:48')
,('300','1','Answer details recorded for survey ref 300 - Q1','01/12/2013','09:08:15')
,('300','2','Answer details recorded for survey ref 300 - Q2','01/12/2013','09:08:25')
GO
-- USUAL COLUMN AND ROW OUTPUT
select * from #PivotTestTable
-- CROSSTAB USING CASE STATEMENTS
SELECT
SURVEY_REF,
MAX(CASE WHEN QUESTION = 1 THEN ANSWER ELSE NULL END) AS Q1,
MAX(CASE WHEN QUESTION = 2 THEN ANSWER ELSE NULL END) AS Q2,
MAX(CASE WHEN QUESTION = 3 THEN ANSWER ELSE NULL END) AS Q3,
MAX(CASE WHEN QUESTION = 4 THEN ANSWER ELSE NULL END) AS Q4,
MAX(CASE WHEN QUESTION = 5 THEN ANSWER ELSE NULL END) AS Q5
FROM #PivotTestTable GROUP BY SURVEY_REF
May 27, 2014 at 7:00 pm
Is using SSRS an option or do you need to do it in T-SQL? The nice thing about doing it in SSRS is that the columns are dynamic.
May 27, 2014 at 11:04 pm
A semi-dynamic query is straight forward
😎
USE tempdb;
GO
--CREATE TEST DATA
IF OBJECT_ID('tempdb..#PivotTestTable') IS NOT NULL DROP TABLE #PivotTestTable
CREATE TABLE #PivotTestTable
(
[SURVEY_REF] [nvarchar](100) NOT NULL
,[QUESTION] [nvarchar](MAX) NULL
,[ANSWER] [nvarchar](100) NULL
,[DATE_REC] [nvarchar](10) NULL
,[TIME_REC] [nvarchar](10) NULL
)
INSERT INTO #PivotTestTable
(
[SURVEY_REF]
,[QUESTION]
,[ANSWER]
,[DATE_REC]
,[TIME_REC]
)
VALUES
('100','1','Answer details recorded for survey ref 100 - Q1','01/01/2014','13:04:02')
,('100','2','Answer details recorded for survey ref 100 - Q2','01/01/2014','13:04:10')
,('100','3','Answer details recorded for survey ref 100 - Q3','01/01/2014','13:04:18')
,('200','1','Answer details recorded for survey ref 200 - Q1','01/02/2014','11:26:28')
,('200','2','Answer details recorded for survey ref 200 - Q2','01/02/2014','11:26:32')
,('200','3','Answer details recorded for survey ref 200 - Q3','01/02/2014','11:26:40')
,('200','4','Answer details recorded for survey ref 200 - Q4','01/02/2014','11:26:44')
,('200','5','Answer details recorded for survey ref 200 - Q5','01/02/2014','11:26:48')
,('300','1','Answer details recorded for survey ref 300 - Q1','01/12/2013','09:08:15')
,('300','2','Answer details recorded for survey ref 300 - Q2','01/12/2013','09:08:25')
GO
DECLARE @SQL_STR NVARCHAR(MAX) = N'';
;WITH QUESTION_LIST AS
(
SELECT
N',MAX(CASE WHEN QUESTION = ' + CAST([QUESTION] AS NVARCHAR(12))
+ N' THEN ANSWER ELSE NULL END) AS Q' + CAST([QUESTION] AS NVARCHAR(12)) AS QSTR
FROM #PivotTestTable
GROUP BY [QUESTION]
)
SELECT @SQL_STR = N'SELECT
SURVEY_REF
' + (
SELECT QSTR
FROM QUESTION_LIST
FOR XML PATH(''),TYPE).value('.[1]','NVARCHAR(MAX)') + N'
FROM #PivotTestTable GROUP BY SURVEY_REF';
EXEC (@SQL_STR);
Results
SURVEY_REF Q1 Q2 Q3 Q4 Q5
----------- ------------------------------------------------ ------------------------------------------------ ------------------------------------------------ ------------------------------------------------ ------------------------------------------------
100 Answer details recorded for survey ref 100 - Q1 Answer details recorded for survey ref 100 - Q2 Answer details recorded for survey ref 100 - Q3 NULL NULL
200 Answer details recorded for survey ref 200 - Q1 Answer details recorded for survey ref 200 - Q2 Answer details recorded for survey ref 200 - Q3 Answer details recorded for survey ref 200 - Q4 Answer details recorded for survey ref 200 - Q5
300 Answer details recorded for survey ref 300 - Q1 Answer details recorded for survey ref 300 - Q2 NULL NULL NULL
May 28, 2014 at 2:34 am
This is exactly what I wanted however I never expected to see the use of FOR XML in the code!
This is much appreciated. I prefer this method as I need to be flexible with my output and didn't want to overcomplicate with SSRS.
Many thanks
😀
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply