Turn a results set on it's side

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)

Turn a results set on it's side

Author

Topic

the1gadget
Yak Posting Veteran

55 Posts

Posted - 2006-04-21 : 06:47:29

Hi All

I am doing a query

Select top 5 CatId from Fubar where id = 1234 order by CatId

And I get a result like

CatID
-----
1
6
12
13
100

But I want the result back as on row like

CatID_1 CatID_2 CatID_3 CatID_4 CatID_5
------- ------- ------- ------- -------
1 6 12 13 100

The other problem is that there may not be 5 results so I would want to get the results back like

CatID_1 CatID_2 CatID_3 CatID_4 CatID_5
------- ------- ------- ------- -------
1 6 12 13 <NULL>

or

CatID_1 CatID_2 CatID_3 CatID_4 CatID_5
------- ------- ------- ------- -------
1 6 12 13

Any one help ?

--
David

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-21 : 07:18:34

Refer this
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

Madhivanan

Failing to plan is Planning to fail

the1gadget
Yak Posting Veteran

55 Posts

Posted - 2006-04-21 : 07:36:00

Hi Madhivanan

Must be me being stupid but I can not see how this takes a result set and rotates it.

--
David

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-21 : 08:52:27

declare @t table(catid int)
insert into @t
select 1 union all select 6 union all select 12 union all
select 13 union all select 100
declare @s varchar(8000)
select @s=Coalesce(@s+',','')+cast(catId as varchar(10)) from @t
exec('Select '+ @s)

Madhivanan

Failing to plan is Planning to fail

the1gadget
Yak Posting Veteran

55 Posts

Posted - 2006-04-21 : 09:29:33

Hi Madhivanan

Now I see.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources