SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Column 1 to resultset header and column 2 to row
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

willbourne
Starting Member

Sweden
7 Posts

Posted - 02/07/2006 :  09:44:07  Show Profile  Send willbourne an ICQ Message  Reply with Quote
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

India
22772 Posts

Posted - 02/07/2006 :  10:23:19  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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 - 02/07/2006 :  10:27:54  Show Profile  Reply with Quote

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




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 - 02/07/2006 :  10:30:23  Show Profile  Reply with Quote
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)

Singapore
17681 Posts

Posted - 02/07/2006 :  22:59:18  Show Profile  Reply with Quote
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)


The Output of print @sql is
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


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



Edited by - khtan on 02/07/2006 23:01:56
Go to Top of Page

willbourne
Starting Member

Sweden
7 Posts

Posted - 02/08/2006 :  03:25:29  Show Profile  Send willbourne an ICQ Message  Reply with Quote
Thank you very much! You´re the best!
Go to Top of Page

plalcheriwt
Starting Member

Liberia
3 Posts

Posted - 05/22/2013 :  13:14:18  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.58 seconds. Powered By: Snitz Forums 2000