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.
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 RegionTO 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.aspxMadhivananFailing to plan is Planning to fail |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2006-02-07 : 10:27:54
|
[code]USE NorthwindGOSET NOCOUNT ONCREATE TABLE myTable99(RegionID char(1), RegionDescription varchar(20))GOINSERT INTO myTable99(RegionID, RegionDescription)SELECT '1', 'Eastern' UNION ALLSELECT '2', 'Western' UNION ALLSELECT '3', 'Northern' UNION ALLSELECT '4', 'Southern'GODECLARE @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 RegionIDSELECT @RegionIDUNION ALL SELECT @RegionDescriptionGOSET NOCOUNT OFFDROP TABLE myTable99GO[/code]Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
X002548
Not Just a Number
15586 Posts |
|
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 Regionorder by RegionIDselect @sql = left(@sql, len(@sql) - 2)select @sql = @sql + char(13) + 'FROM Region'print @sqlexec (@sql)[/code]The Output of print @sql is [code] SELECTmax(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' |
|
|
willbourne
Starting Member
7 Posts |
Posted - 2006-02-08 : 03:25:29
|
Thank you very much! You´re the best! |
|
|
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 |
|
|
|
|
|
|
|