November 12, 2008 at 11:16 am
Hi - I've a question that I'm pretty sure can be done through a simple SQL query, but I'm not entirely sure how to go about it.
I have data in the following format, as an example:
Name Question Answer
A Q1 A1
A Q2 A2
A Q3 A3
A Q4 A4
B Q1 A5
B Q2 A6
B Q3 A7
B Q4 A8
C Q1 A1
C Q2 A5
C Q3 A9
C Q4 A10
D Q1 A5
D Q2 A6
D Q3 A9
D Q4 A10
Basically, the user info is in the first column, the questions are in the second column, and the answers in the third.
What I want to do is transform it into the following:
Question A B C D
Q1 A1 A5 A1 A5
Q2 A2 A6 A5 A6
Q3 A3 A7 A9 A9
Q4 A4 A8 A8 A10
So, basically, take the users, and transform them from a column into a row.
As far as I can remember, I should be able to do this through a Pivot, but I can't quite figure out how to go about it.
Any help would be appreciated!
November 12, 2008 at 2:00 pm
This is what the code looks like (top portion is just to generate your sample data). Hope it helps.
DECLARE @Tbl AS TABLE (
[Name] VARCHAR(10),
Question VARCHAR(10),
Answer VARCHAR(10)
)
INSERT INTO @Tbl
SELECT 'A', 'Q1', 'A1'
UNION ALL
SELECT 'A', 'Q2', 'A2'
UNION ALL
SELECT 'A', 'Q3', 'A3'
UNION ALL
SELECT 'A', 'Q4', 'A4'
UNION ALL
SELECT 'B', 'Q1', 'A5'
UNION ALL
SELECT 'B', 'Q2', 'A6'
UNION ALL
SELECT 'B', 'Q3', 'A7'
UNION ALL
SELECT 'B', 'Q4', 'A8'
UNION ALL
SELECT 'C', 'Q1', 'A1'
UNION ALL
SELECT 'C', 'Q2', 'A5'
UNION ALL
SELECT 'C', 'Q3', 'A9'
UNION ALL
SELECT 'C', 'Q4', 'A10'
UNION ALL
SELECT 'D', 'Q1', 'A5'
UNION ALL
SELECT 'D', 'Q2', 'A6'
UNION ALL
SELECT 'D', 'Q3', 'A9'
UNION ALL
SELECT 'D', 'Q4', 'A10'
SELECT Question, [A] AS A, AS B, [C] AS C, [D] AS D
FROM
(SELECT [Name], Question, Answer
FROM @Tbl) P
PIVOT (MAX(Answer)
FOR [Name] IN ([A], , [C], [D])
) AS PVT
November 12, 2008 at 2:13 pm
alright, that works for this specific example, but is there any way to do this in a fashion that doesn't require me to explicitly declare the names of the columns in the fashion you provided? IE, rather than:
SELECT Question, [A] AS A, AS B, [C] AS C, [D] AS D
have some sort of way whereby I can have the A, B, C, and D parts generated through a query? because, ultimately, what I need to do with this, is take a list of about 80 distinct "users" and transform them into columns, and while I could of course just explicitly name them A, B, C, D, etc, all the way up to 80, it would be nice if I could generate this in some fashion.
November 12, 2008 at 4:31 pm
I think you can get there. Basically you have to write code that creates a dynamic SQL string similar to the one in the example, and then execute it. You would start by doing a select distinct of your names and storing them in a table variable or temp table. Then you would go through that list to supply your column names to the dynamic SQL string.
Hope the concept helps. Sorry I don't have time to code an example for you right now.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply