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
 Transact-SQL (2000)
 Column 1 to resultset header and column 2 to row

Author  Topic 

willbourne
Starting Member

7 Posts

Posted - 2006-02-07 : 09:44:07
I am looking for a dynamic way to make column 1 (RegionID) resultset header and column 2 (RegionDescription) a row.

FROM THIS:
SELECT RegionID, RegionDescription FROM Region

TO THIS:
[1 ] [2 ] [3 ] [4 ]
[Eastern] [Western] [Northern] [Southern]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-07 : 10:23:19
http://weblogs.sqlteam.com/jeffs/archive/2005/05.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2006-02-07 : 10:27:54
[code]
USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE myTable99(RegionID char(1), RegionDescription varchar(20))
GO

INSERT INTO myTable99(RegionID, RegionDescription)
SELECT '1', 'Eastern' UNION ALL
SELECT '2', 'Western' UNION ALL
SELECT '3', 'Northern' UNION ALL
SELECT '4', 'Southern'
GO

DECLARE @RegionID varchar(8000), @RegionDescription varchar(8000)

SELECT @RegionID = COALESCE(@RegionId + ', ','') + RegionID
FROM myTable99
ORDER BY RegionID
SELECT @RegionDescription = COALESCE(@RegionDescription + ', ','') + RegionDescription
FROM myTable99
ORDER BY RegionID

SELECT @RegionID
UNION ALL
SELECT @RegionDescription
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

[/code]


Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2006-02-07 : 10:30:23
Did we forget to say that this was a presentation issue?

I'll blog it though



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-07 : 22:59:18
[code]declare @sql nvarchar(1024)
select @sql = 'SELECT' + char(13)
select @sql = @sql + 'max(case when RegionID = '
+ convert(varchar(10), RegionID)
+ ' then RegionDescription else null end) as ['
+ convert(varchar(10), RegionID)
+ '],'
+ char(13)
from Region
order by RegionID

select @sql = left(@sql, len(@sql) - 2)
select @sql = @sql + char(13) + 'FROM Region'

print @sql

exec (@sql)[/code]

The Output of print @sql is
[code] SELECT
max(case when RegionID = 1 then RegionDescription else null end) as [1],
max(case when RegionID = 2 then RegionDescription else null end) as [2],
max(case when RegionID = 3 then RegionDescription else null end) as [3],
max(case when RegionID = 4 then RegionDescription else null end) as [4]
FROM Region[/code]

----------------------------------
'KH'


Go to Top of Page

willbourne
Starting Member

7 Posts

Posted - 2006-02-08 : 03:25:29
Thank you very much! You´re the best!
Go to Top of Page

plalcheriwt
Starting Member

3 Posts

Posted - 2013-05-22 : 13:14:18
20gb mp4 phenomenological sociology husky mats microsoft excel training rival smartpot stylewriter orcale bones pillers ice scoop bouncy castle hire [url=http://www.suprajpshoesmart.com/]????? ????[/url]
large tvs fragrance review philips stereo our children forever allied product games show yard ornaments norton antivirus 2003 professional crack repacking samrt [url=http://www.newbalance996shoesjp.com/]??????? ?????[/url]
photoderm ipl return of the king walkthrough mio c310 gps street child amd k6 2 500 small wall oven mimi jobe brainsell [url=http://www.viviennewestwoodsaihu2013.com/]?????? ????[/url]
retellings onkyo 674 olympus c460 6233 living your best life
Go to Top of Page
   

- Advertisement -