Convert first column in row without duplicate values, and second column in rows

Question

Convert first column in row without duplicate values, and second column in rows

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Y.B. - Thursday, February 16, 2017 1:18 PM
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
@myTable
GROUP 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,

That would only be true if there were an "ID" column like you have. No such column exists in the original data the OP provided and you can't just make one with ROW_NUMBER because there's no guarantee the order will be correct for the data provided.

--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 SSC Guru Points: 1004406 More actions · Answer 2

Luis Cazares - Friday, February 17, 2017 5:44 AM
benkraiemchedlia - Friday, February 17, 2017 12:31 AM
Jeff Moden - Thursday, February 16, 2017 10:39 PM
benkraiemchedlia - Thursday, February 16, 2017 12:59 PM
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 |gg
what I want is this
question1 | question 2 | question 3
aa | bb | cc
ee | ff | null
gg | null | null
I think that pivot table will be possible from 3 columns.
Any ideas please ?
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.
Becaus in answer table I have a column named questionID
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

+1000 😉 Didn't read this before I started rattling off the same thing.

--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 SSC Guru Points: 1004406 More actions · Answer 3

benkraiemchedlia - Friday, February 17, 2017 9:39 AM
Y.B. - Friday, February 17, 2017 7:53 AM
benkraiemchedlia - Friday, February 17, 2017 6:10 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
As 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

To reiterate what Luis and I have been trying to tell you, your request is absolutely doomed to failure because you're missing anything and everything to group questions together. You need a table that does provides such grouping identifiers for each "poll". If someone else wrote this, there might be a chance of such a thing already existing. If you wrote this and can't find one, you need to make one for any of this to be successful.

If the graphics above are for a single "poll", how do you keep track of multiple polls with more than one "Question 1"? We need to know that so we can help you answer your original question properly so that it won't blow up 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)

Y.B. SSChampion Points: 11649 More actions · Answer 4

Jeff Moden - Friday, February 17, 2017 8:35 PM
That would only be true if there were an "ID" column like you have. No such column exists in the original data the OP provided and you can't just make one with ROW_NUMBER because there's no guarantee the order will be correct for the data provided.

LOL...obviously my phrasing sucked because everybody repeated the same thing I thought I had already said. I never meant to suggest that you can just add a row_number to solve the op's problem. What I should have said is look how easy it can be when you have a proper table!

SELECT quote FROM brain WHERE original = 1
0 rows returned