CODEWITHSUNDEEP
sql-serversql-server-2012
G Wizard
G Wizard

Reputation: 45

Converting SQL Rows into Columns

I'm having some difficulty understanding the best approach to get the following result set.

I have a result set (thousands of rows) that I want to update from:

ID    Question   Answer
---    --------  --------
1      Business    NULL
1      Job         Other
1      Location    UK
2      Business    Legal
3      Location    US
4      Location    UK

To This:

ID     Buisness   Job    Location  
---    --------   ---    --------
1      NULL       Other  UK
2      Legal      NULL   NULL
3      NULL       NULL   US
4      NULL       NULL   UK

I have been looking at SELF JOINS and PIVOT tables but wanted to understand the best method as I have not been able to achieve the desired output.

Thanks Gary

Upvotes: 1

Views: 39

Answers (2)

James Z
James Z

Reputation: 12317

If you want to use pivot, you can do it like this:

CREATE TABLE #Table1
    ([ID] int, [Question] varchar(8), [Answer] varchar(5))
;

INSERT INTO #Table1
    ([ID], [Question], [Answer])
VALUES
    (1, 'Business', NULL),
    (1, 'Job', 'Other'),
    (1, 'Location', 'UK'),
    (2, 'Business', 'Legal'),
    (3, 'Location', 'US'),
    (4, 'Location', 'UK')
;

select * from 
(select * from #Table1) S
pivot (
  max(Answer) for Question in (Business, Job, Location)
) P

Upvotes: 2

TheGameiswar
TheGameiswar

Reputation: 28900

select
id,
max(case when question='business' then answer end) 'business',
max(case when question='Job' then answer end) 'Job',
max(case when question='Location' then answer end) 'Location'
group by id

Upvotes: 1

Related Questions