Displaying rows as columns - 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 2000 Forums

SQL Server Development (2000)

Displaying rows as columns

Author

Topic

sagarpbora
Starting Member

12 Posts

Posted - 2007-04-09 : 07:20:54

Hi All,
I am working on a stored procedure, one part of which contains retrieving some rows from one table and display them as columns in the result.
e.g.
===============
ID |QN | QR |
===============
1 |1 | Yes |
1 |2 | No |
1 |3 | Yes |
1 |4 | Yes |

the result I am looking for is like this

========================
ID |Q1 | Q2 | Q3 | Q4 |
========================
1 |Yes | No | Yes | Yes |

Can anybody have any idea about this?
Please let me know the suggestions.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-09 : 07:25:25

[code]-- Prepare sample data
declare @t table
(
QN int,
QR varchar(10)
)

insert @t
select 1, 'Yes' union all
select 2, 'No' union all
select 3, 'Yes' union all
select 4, 'Yes'

-- Final query
select
max(case QN when 1 then QR end) as Q1,
max(case QN when 2 then QR end) as Q2,
max(case QN when 3 then QR end) as Q3,
max(case QN when 4 then QR end) as Q4
from @t[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

sagarpbora
Starting Member

12 Posts

Posted - 2007-04-09 : 07:30:07

hey...
please find the updated post...
actually, all the schema is ready.I am not supposed to change the contents of the table or alter the structure.Even, I cant insert data in the table.
I just need to obtain the data from different tables and compile them in a single result table as shown. Is there any way to do it?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-09 : 07:33:48

[code]select
ID,
max(case QN when 1 then QR end) as Q1,
max(case QN when 2 then QR end) as Q2,
max(case QN when 3 then QR end) as Q3,
max(case QN when 4 then QR end) as Q4
from SomeTable
Group by ID[/code]

FYI, first part in my previous reply was just to create sample data. You can just use the final query I shown here.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

sagarpbora
Starting Member

12 Posts

Posted - 2007-04-09 : 07:51:54

Thanks a ton man!!!
Its working...instead of 4, I am having 41 questions...

Thanks again!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources