December 2, 2004 at 8:03 am
I have an intermediate result (a virtual table) which looks like this:
NumberInAgeGroup AgeGroupName
102 youngsters
504 middleaged
706 retired
I need to pivot this output and get the AgeGroupName as columnheader to
NumberInAgeGroup.
Thus:
youngsters middleaged retired
102 504 706
Ideas?
/m
December 3, 2004 at 1:41 am
Hello /m
Maybe like this?
Create Table #tmpAgeGroups (NumberInAgeGroup int, AgeGroupName varchar(15))
Insert into #tmpAgeGroups (NumberInAgeGroup, AgeGroupName)
Values(102, 'youngsters')
Insert into #tmpAgeGroups (NumberInAgeGroup, AgeGroupName)
Values(504, 'middleaged')
Insert into #tmpAgeGroups (NumberInAgeGroup, AgeGroupName)
Values(706, 'retired')
Declare @LastGroup varchar(15)
Declare @SQL nvarchar(4000)
set @SQL = ''
Set @LastGroup = (Select top 1 AgeGroupName From #tmpAgeGroups Order By AgeGroupName)
While @LastGroup is not null
Begin
If len(@SQL)> 0 Set @SQL = @SQL + ', '
Set @SQL = @SQL + '(SELECT ' + @LastGroup + '=NumberInAgeGroup From #tmpAgeGroups Where AgeGroupName = ''' + @LastGroup + ''') ' + @LastGroup
Set @LastGroup = (Select top 1 AgeGroupName From #tmpAgeGroups Where AgeGroupName > @LastGroup Order By AgeGroupName)
End
If len(@SQL)> 0
Begin
Set @SQL = 'SELECT * FROM ' + @SQL
Exec sp_Executesql @SQL
End
Drop Table #tmpAgeGroups
Regards,
Leon Bakkers
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply