March 21, 2012 at 7:23 am
I have a table like this
ChoiceId| ChoiceName |QuestionId
101655|3 Months | 15418
101656|6 Months | 15418
101657|1 Year | 15418
101658|Whenever Necessary| 15418
101659|Every Month | 15418
101660|No Audit Required | 15418
I need ouptput to be in the following structure
QuestionId | Choice1 | Choice2 | Choice3 | Choice4 | Choice5 | Choice6
15418 |3 Months | 6 Months | 1 Year | Whenever Necessary | Every Month | No Audit
It would be great if any one can provide me SQL query to do the same..
March 21, 2012 at 7:45 am
--Your sample data
SELECT ChoiceId, ChoiceName, QuestionId
INTO yourTable
FROM (VALUES(101655,'3 Months',15418),(101656,'6 Months',15418),
(101657,'1 Year',15418),(101658,'Whenever Necessary',15418),
(101659,'Every Month',15418),(101660,'No Audit Required',15418)
)a(ChoiceId, ChoiceName, QuestionId);
--Actual solution
DECLARE @SQL AS NVARCHAR(MAX);
SELECT @SQL = COALESCE(@SQL,'') + ',' + CHAR(13) + CHAR(10) + 'MAX(CASE WHEN pos = ' + CHAR(39) + pos + CHAR(39) + ' THEN ChoiceName ELSE NULL END) AS ' + QUOTENAME(pos)
FROM (SELECT DISTINCT
'Choice'+CAST(ROW_NUMBER() OVER(PARTITION BY QuestionId ORDER BY ChoiceId) AS VARCHAR(2)) AS pos
FROM yourTable) a;
SET @SQL = 'SELECT QuestionId' + @SQL + CHAR(13) + CHAR(10) +
'FROM (SELECT QuestionId, ChoiceName,' + CHAR(13) + CHAR(10) +
+CHAR(39)+'Choice'+CHAR(39)+'+CAST(ROW_NUMBER() OVER(PARTITION BY QuestionId ORDER BY ChoiceId) AS VARCHAR(2)) AS pos'+ CHAR(13) + CHAR(10) +
'FROM yourTable) a' + CHAR(13) + CHAR(10) +
'GROUP BY QuestionId;'
--See what the dynamic SQL produces!
PRINT @SQL;
--Now execute it to check the results
EXECUTE sp_executesql @SQL;
SQL produced by the dynamic SQL statement: -
SELECT QuestionId,
MAX(CASE WHEN pos = 'Choice1' THEN ChoiceName ELSE NULL END) AS [Choice1],
MAX(CASE WHEN pos = 'Choice2' THEN ChoiceName ELSE NULL END) AS [Choice2],
MAX(CASE WHEN pos = 'Choice3' THEN ChoiceName ELSE NULL END) AS [Choice3],
MAX(CASE WHEN pos = 'Choice4' THEN ChoiceName ELSE NULL END) AS [Choice4],
MAX(CASE WHEN pos = 'Choice5' THEN ChoiceName ELSE NULL END) AS [Choice5],
MAX(CASE WHEN pos = 'Choice6' THEN ChoiceName ELSE NULL END) AS [Choice6]
FROM (SELECT QuestionId, ChoiceName,
'Choice'+CAST(ROW_NUMBER() OVER(PARTITION BY QuestionId ORDER BY ChoiceId) AS VARCHAR(2)) AS pos
FROM yourTable) a
GROUP BY QuestionId;
Results of executing the dynamic SQL: -
QuestionId Choice1 Choice2 Choice3 Choice4 Choice5 Choice6
----------- ------------------ ------------------ ------------------ ------------------ ------------------ ------------------
15418 3 Months 6 Months 1 Year Whenever Necessary Every Month No Audit Required
References --> Part 1 of Cross Tabs and Pivots article[/url],Part 2 of Cross Tabs and Pivots article[/url]
March 21, 2012 at 11:51 am
Works like a charm... 🙂 Thanks mate
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply