PIVOT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

PIVOT

Author

Topic

mchljrdn
Starting Member

1 Post

Posted - 2009-04-10 : 10:28:18

I'm receiving data from a form in a question - answer format. Is it possible to make view of the data showing with the question as column headers and the response as row data. Short data sample below.

SessionID Question Response
1 Name Mike
1 City Tulsa
1 State OK
1 Device Hammer

I want to view the data like this

SessionID Name City State Device
1 Mike Tulsa OK Hammer

Michael

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-11 : 02:43:00

SELECT SessionID,
MAX(CASE WHEN Question = 'Name' THEN Response ELSE NULL END) AS Name,
MAX(CASE WHEN Question = 'City' THEN Response ELSE NULL END) AS City,
MAX(CASE WHEN Question = 'State' THEN Response ELSE NULL END) AS State,
MAX(CASE WHEN Question = 'Device' THEN Response ELSE NULL END) AS Device
FROM Table1
GROUP BY SessionID

E 12°55'05.63"
N 56°04'39.26"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-13 : 08:57:48

try this once too
select sessionid,[name],[city],[state],[device]
from urtable
pivot (max(response) for question in ( [name],[city],[state],[device])) as pvt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources