Rows to columns.....possible? - 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

General SQL Server Forums

New to SQL Server Programming

Rows to columns.....possible?

Author

Topic

lec7grs
Starting Member

2 Posts

Posted - 2012-07-27 : 06:14:24

Hi there,

Here is a simple example of something I am trying to figure out in MS SQL.

I have a table with the following data:

QID RESP
1 a
1 b
1 c
1 d
2 e
2 f
2 g
2 h
3 i
3 j
3 k
3 l

And I would like it to look like this:

1 2 3
a e i
b f j
c g k
d h l

With the distinct values in the QID column in the first table becoming the column headers in the new table.

I've tried various things with PIVOT but cannot get all the data retained in the output view.

Any help would be appreciated!

Thanks

lec7grs

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-07-27 : 06:20:39

[code]
select [1], [2], [3]
from (
select QID, RESP, rn = row_number() over (partition by QID order by RESP)
from yourtable
) t
pivot
(
max(RESP)
for QID in ([1], [2], [3])
) p
[/code]

KH
[spoiler]Time is always against us[/spoiler]

lec7grs
Starting Member

2 Posts

Posted - 2012-07-27 : 07:02:06

Many thanks for your response - that works perfectly.

Now, assuming QID has a finite but unknown distinct list of values - say ~1500 is there any way of making the selection dynamic?

Thanks

Graham

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-07-27 : 07:06:14

you can consider using Dynamic SQL to do it
http://www.sommarskog.se/dynamic_sql.html

KH
[spoiler]Time is always against us[/spoiler]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-07-28 : 13:50:00

the whole bandwidth + computation power balance has shifted since mainframe days. This simply isn't a task you should be asking a database server to do now....

Simply order the output list by the keys and then pivot them in your application layer. it's got *much* more room to scale.

PIVOT is my least favourite operator.

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources