SQL Query with CTE or other...!
-
Any one can help me please with this issue? I really appreciate this help. The number of Answer is dynamic.
- This topic was modified 5 years, 1 month ago by carloscris.
- This topic was modified 5 years, 1 month ago by carloscris.
-
You could use a dynamic cross-tab query. Take a look at the following 2 articles by Jeff Moden
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
-
You can achieve this using the below query:
SELECT ID, Question, [A], , [C], [OPTION]
FROM
(
SELECT A.ID,Answer,Question
FROM Answer A
inner join Question Q on A.ID = Q.QuestionID
) As PivotTable
PIVOT
(
MAX(Answer)
FOR Answer IN ([A], , [C], [OPTION])
) AS P
-
Hi Sumathi,
I use this query all ready and work. But the problem is because we never know how many column can be...idea is create it automatically. And other issue is concatenate all in just one single column like: Q1 >>> |A, B, C| in same column.
Many thanks
-
Maybe this will give you what you want:
--** Data set up ***************************************************************
CREATE TABLE dbo.questions (
question_id int PRIMARY KEY,
question varchar(1000) NOT NULL
)
INSERT INTO dbo.questions VALUES
(1, 'Q1'), (2, 'Q2'),
(3, 'Q3'), (4, 'Q4')
CREATE TABLE dbo.answers (
question_id int NOT NULL,
answer_sequence smallint NOT NULL,
answer varchar(1000) NOT NULL
)
TRUNCATE TABLE dbo.answers
INSERT INTO dbo.answers VALUES
(1, 10, 'A'), (1, 20, 'B'), (1, 30, 'C'), (1, 40, 'OPTION'),
(2, 10, 'A'), (2, 20, 'B'), (2, 30, 'C'),
(3, 10, 'E'), (3, 20, 'F')
--** Actual code ***************************************************************
DECLARE @max_number_of_answers smallint
DECLARE @sql varchar(max)
SELECT @max_number_of_answers = (
SELECT TOP (1) COUNT(*)
FROM answers
GROUP BY question_id
ORDER BY COUNT(*) DESC )
--PRINT @max_number_of_answers
;WITH
cte_tally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally1000 AS (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number
FROM cte_tally10 c1
CROSS JOIN cte_tally10 c2 CROSS JOIN cte_tally10 c3
)
SELECT @sql = STUFF(CAST((
SELECT REPLACE(', MAX(CASE WHEN answer_number = ? THEN answer ELSE '''' END)' +
' AS answer?', '?', CAST(t.number AS varchar(5)))
FROM cte_tally1000 t
WHERE t.number <= @max_number_of_answers
ORDER BY t.number
FOR XML PATH(''), TYPE
) AS varchar(max)), 1, 2, '')
--PRINT @sql
SET @sql = 'SELECT a.question_id, ' + @sql + ' FROM ( ' +
'SELECT *, ROW_NUMBER() OVER(PARTITION BY question_id
ORDER BY answer_sequence) AS answer_number ' +
'FROM dbo.answers ) AS a ' +
'INNER JOIN dbo.questions q ON q.question_id = a.question_id ' +
'GROUP BY a.question_id ' +
'ORDER BY question_id '
--PRINT @sql
EXEC(@sql)
INSERT INTO dbo.answers VALUES(2, 40, 'D'),(2, 50, 'E'), (2, 60, 'F'), (2, 70, 'G')
SELECT 'Added 4 answers for Q#2!'
SELECT @max_number_of_answers = (
SELECT TOP (1) COUNT(*)
FROM answers
GROUP BY question_id
ORDER BY COUNT(*) DESC )
;WITH
cte_tally10 AS (
SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally1000 AS (
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number
FROM cte_tally10 c1
CROSS JOIN cte_tally10 c2 CROSS JOIN cte_tally10 c3
)
SELECT @sql = STUFF(CAST((
SELECT REPLACE(', MAX(CASE WHEN answer_number = ? THEN answer ELSE '''' END)' +
' AS answer?', '?', CAST(t.number AS varchar(5)))
FROM cte_tally1000 t
WHERE t.number <= @max_number_of_answers
ORDER BY t.number
FOR XML PATH(''), TYPE
) AS varchar(max)), 1, 2, '')
--PRINT @sql
SET @sql = 'SELECT a.question_id, ' + @sql + ' FROM ( ' +
'SELECT *, ROW_NUMBER() OVER(PARTITION BY question_id
ORDER BY answer_sequence) AS answer_number ' +
'FROM dbo.answers ) AS a ' +
'INNER JOIN dbo.questions q ON q.question_id = a.question_id ' +
'GROUP BY a.question_id ' +
'ORDER BY question_id '
--PRINT @sql
EXEC(@sql)SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Can the pivoting be done in Excel or whatever reporting tool your company uses? While I have seen the dynamic SQL type solutions work for this, it's probably best to do this type of pivot outside of the database.
-
No Chris,
I have to be done in SQL Server query.
Thanks
-
It is easy enough, if you want one column for your answer:
DECLARE @Questions TABLE ([ID] int, [Question] VARCHAR(64))
INSERT INTO @Questions VALUES(1,'Q1'),(2,'Q2'),(3,'Q3'),(4,'Q4');
DECLARE @Answers TABLE ([ID] int, [Answer] VARCHAR(64))
INSERT INTO @Answers VALUES(1,'A'),(1,'B'),(1,'C'),(1,'OPTION'),(2,'A'),(2,'B'),(2,'C'),(3,'E'),(3,'F');
SELECT
q.[Question]
,STUFF((
SELECT CONCAT(',',a.[Answer]) FROM @Answers a
WHERE q.[ID] = a.[ID]
FOR XML PATH('')
),1,1,'') AS [Answers]
FROM
@Questions qThis should work on SQL2012+.
If you were using SQL 2017, you could use STRING_AGG() rather than the STUFF/FOR XML trick
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply