Convert first column in row without duplicate values, and second column in rows
-
Hi,
I have a table with 2 columns : questions, answers. The same questions can be answered by many users
so my first table is a join between table question and table answer and give me this result :question | answer
question 1 | aa
question 2 | bb
question 3 | cc
question 1 | ee
question 2 | ff
question1 |ggwhat I want is this
question1 | question 2 | question 3
aa | bb | cc
ee | ff | null
gg | null | nullI think that pivot table will be possible from 3 columns.
Any ideas please ? -
Please post consumable information, such as CREATE TABLE statements and the INSERTS for your sample data. You've provided expected results, but you haven't said WHY you want that particular format, nor have you made any mention of tying an identifier associated with a given answerer of the various questions to the answers, nor whether or not that matters.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
A simple crosstab will do. You will need to use dynamic SQL if you want to handle an unknown amount of 'questions'. Noticed I added and ID field. When dealing with such a table you need an identifier that tells you which group of questions/answers belong together. Also please use my example for future questions on how to provide DDL and sample data. Trust me you will get help much quicker that way.
DECLARE @myTable TABLE (ID INT, question VARCHAR(50), answer VARCHAR(50))INSERT INTO @myTable
VALUES (1, 'question 1', 'aa'), (1, 'question 2', 'bb'), (1, 'question 3', 'cc'), (2, 'question 1', 'dd'), (2, 'question 2', 'ee'), (3, 'question 1', 'ff')SELECT
ID,
MAX(CASE WHEN question = 'question 1' THEN answer END) AS 'question 1',
MAX(CASE WHEN question = 'question 2' THEN answer END) AS 'question 2',
MAX(CASE WHEN question = 'question 3' THEN answer END) AS 'question 3'FROM
@myTableGROUP BY ID
Here is a Dynamic SQL example.
CREATE TABLE #myTable (ID INT, question VARCHAR(50), answer VARCHAR(50))
DECLARE @sql VARCHAR(MAX)INSERT INTO #myTable
VALUES (1, 'question 1', 'aa'), (1, 'question 2', 'bb'), (1, 'question 3', 'cc'), (2, 'question 1', 'dd'), (2, 'question 2', 'ee'), (3, 'question 1', 'ff');WITH myValues (n) AS
(
SELECT DISTINCT question FROM #myTable
)
SELECT @sql = '
SELECT
ID' +
(SELECT ' , MAX(CASE WHEN question = ' + '''' + n + '''' + ' THEN answer END) AS ' + '''' + n + ''''
FROM myValues FOR XML PATH(''),TYPE).value('./text()[1]', 'nvarchar(MAX)'
)
+ ' FROM
#myTable
GROUP BY ID'EXECUTE (@sql)
DROP TABLE #myTable
Cheers,
SELECT quote FROM brain WHERE original = 1
0 rows returned -
How do you know which answers go on the same row? Remember that tables aren't ordered by default.
- benkraiemchedlia - Thursday, February 16, 2017 12:59 PM
Just to reinforce what Luis stated, there's nothing in the original table to guarantee the order. If you can't establish that, this problem cannot be solved reliably.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Thursday, February 16, 2017 10:39 PM
Becaus in answer table I have a column named questionID
- Y.B. - Thursday, February 16, 2017 1:18 PM
Thank you, I tried the first solution, work like a charm, I will try the second one because, my questions number may be 100 and I will be able to do it dynamically
- benkraiemchedlia - Friday, February 17, 2017 12:31 AM
But you still need something that groups the questions to form a row. Something like:
polll | question | answer
1 | question 1 | aa
1 | question 2 | bb
1 | question 3 | cc
2 | question 1 | ee
2 | question 2 | ff
3 | question 1 | gg - Luis Cazares - Friday, February 17, 2017 5:44 AM
You are right, I m confused, the hole DB was develpped by another person, I have to make reports related to client answers, I have a join between question and answer table, and surveysession table , when a client answer survey (many questions for example 5 ) he can answer 3 of 5 and I have the same surveysession for the 3 answered, the problem is that the 2 non answered questions will not be visible in my result instead of appering and answer is set to null
- benkraiemchedlia - Friday, February 17, 2017 5:54 AM
-
The problem is a client may or not respond to all the questions, in this case I will have a set just for answered questions, these are my tables
in my case I have 23 questions, only 18 are answered
- benkraiemchedlia - Friday, February 17, 2017 6:10 AMThe problem is a client may or not respond to all the questions, in this case I will have a set just for answered questions, these are my tables
in my case I have 23 questions, only 18 are answeredAs I eluded to in my original post you need to have an identifier. Based on the image you provided it would appear like sessionID is what you may be looking for. When the answers are added to the table is the sessionID unique per person?
SELECT quote FROM brain WHERE original = 1
0 rows returned - Y.B. - Friday, February 17, 2017 7:53 AMbenkraiemchedlia - Friday, February 17, 2017 6:10 AMThe problem is a client may or not respond to all the questions, in this case I will have a set just for answered questions, these are my tables
in my case I have 23 questions, only 18 are answeredAs I eluded to in my original post you need to have an identifier. Based on the image you provided it would appear like sessionID is what you may be looking for. When the answers are added to the table is the sessionID unique per person?
Yes it's unique by person, but if a person didn't answer question x the question will not appear, I have to find it with empty value
- benkraiemchedlia - Friday, February 17, 2017 9:39 AM
That shouldn't be a problem. Just do a left join from your question table to your answer table.
DECLARE @questions TABLE (QuestionID INT, question VARCHAR(50))
DECLARE @answers TABLE (SessionID INT, QuestionID INT, Answer VARCHAR(50))INSERT INTO @questions
VALUES (1, 'question 1'), (2, 'question 2'), (3, 'question 3'), (4, 'question 4')INSERT INTO @answers
VALUES (1, 1, 'aaa'), (1, 2, 'bbb'), (1, 3, 'ccc'), (2, 2, 'ddd'), (2, 3, 'eee'), (2, 4, 'fff'), (3, 1, 'ggg'), (3, 4, 'hhh')
SELECT
SessionID,
MAX(CASE WHEN question = 'question 1' THEN answer END) AS 'question 1',
MAX(CASE WHEN question = 'question 2' THEN answer END) AS 'question 2',
MAX(CASE WHEN question = 'question 3' THEN answer END) AS 'question 3',
MAX(CASE WHEN question = 'question 4' THEN answer END) AS 'question 4'FROM
@questions q
LEFT JOIN @answers a ON a.QuestionID = q.QuestionIDGROUP BY SessionID
SELECT quote FROM brain WHERE original = 1
0 rows returned - benkraiemchedlia - Friday, February 17, 2017 12:31 AM
If you're talking about things like "question 1", then that doesn't enforce the order of the rows you've given because there's more than one "question 1". You need something unique to identify each group of questions and answers that you want to pivot.
If you're talking about a totally separate "grouping" column, please add it to your data example so that we can ensure that your results won't go haywire in the near future.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply