Rows to Columns

crazeydazey · April 6th, 2009, 11:15 AM

I'm sure this has been asked many many times, but I can't find the answer to problem..

I have a some SQL the returns the following data..

Code:

id    result
11     1
11     2
11     3
22     1
22     3

I want to change it so it spits out...

Code:

id    result1     result2     result3
11     1               2            3
22     1              NULL          3

seems quite easy, I thought I could do it with a pivot, but I'm a bit stuck..

cheers

Daz

rstelma · April 6th, 2009, 11:52 AM

What have you tried so far?

Here is a good link:

http://www.sqlservercentral.com/articles/T-SQL/63681/

peace95 · April 6th, 2009, 09:31 PM

Crazeydazey:
It is not clear what you are trying to accomplish, i.e. build a new table or generate a report. My response is based on generating a report from your table.

Code:

SELECT DISTINCT id, result

Generated Report:
id result
11 1 2 3
22 1 3

However, the above type of report is usually used to combine the results of a survey or questionnaire, in which case the folloing SQL statement would be used:

Code:

SELECT DISTINCT id, COUNT(result)

Generated Report:
id Count(1) Count(2) Count(3)
11 1 1 1
22 1 1

Hope this helps.

urtrivedi · April 7th, 2009, 01:00 AM

Following query may help

select id,
max(case when result=1 then 1 else null end) result1,
max(case when result=2 then 2 else null end) result2,
max(case when result=3 then 3 else null end) result3
from table1
group by id

This query is applicable to mssql, mysql and oracle 10g

crazeydazey · April 7th, 2009, 02:39 AM

thanks everyone for your quick responses...

I managed to work it out in the end

(I guess I should have updated this thread... sorry)

I did it the same way as urtrivedi did.. I wasn't sure at the time that this was the best way of doing it (it seems a bit of a hack), but it is nice to know that's how someone else would approach it too..

again cheers everyone...

peace95 · April 7th, 2009, 11:26 PM

Craazydazey:
Please let me know what happens when your result(s) set has values more than the finite set 1,2, and 3. For example, result is greater than 4 ...... n, where n is a whole number. Or will you have to build the max-statement for each result outside of your finite set or your procedure will automatically build the max statement?

Thanks.

crazeydazey · April 8th, 2009, 02:49 AM

It's fine, there will only ever be a max of 3 in the result set..

the result is just an ID to a look up table of contact types..

1 = Email
2 = SMS
3 = Telephone

I was just trying to see what contact method a client had and if they had more than 1 it returned multple rows, when I wanted 1 row with 4 columns (clientid, hasEmail, hasSMS, hasTelephone)..