CODEWITHSUNDEEP
sqlt-sqlsql-server-2000dynamic-sqlcrosstab
user2766661
user2766661

Reputation: 41

Dynamic crosstab in SQL Server 2000

I have a table with data that looks like this:

pageno  groupid
100100  mpadilla
100101  Mine
105101  swachman
100100  swachman
105101  Mine
107101  mpadilla

I want the data to look like this (crosstab/pivot) in SQL Server 2000 (no PIVOT available in SQL Server 2000):

pageno  Mine    mpadilla        swachman
100100  --          X           X
100101  X           --          --
105101  X           --          X
107101  --          X           --

Here is my static solution based on this tutorial http://msdn.microsoft.com/en-us/library/aa172756.aspx.:

SELECT pageno, 
    [Mine] = MAX(CASE groupid WHEN 'Mine' THEN 'X' ELSE '--' END),
    [mpadilla] = MAX(CASE groupid WHEN 'mpadilla' THEN 'X' ELSE '--' END),
    [swachman] = MAX(CASE groupid WHEN 'swachman' THEN 'X' ELSE '--' END)
FROM #DistinctPages
GROUP BY pageno
ORDER BY pageno

What I need is a dynamic crosstab solution for SQL Server 2000. I tried adapting this to my situation but I'm just not getting it: Rows to Columns in SQL Server 2000

Any guidance would be helpful!

Upvotes: 2

Views: 452

Answers (1)

t-clausen.dk
t-clausen.dk

Reputation: 44336

declare @a varchar(4000)
set @a = ''
select @a = @a + x
from
(select top 100 percent
',[' +groupid + ']=MAX(CASE groupid WHEN ''' +groupid+ '''  THEN ''X'' ELSE ''--'' END)' x
from #DistinctPages Group by groupid order by groupid) t

exec ('SELECT pageno' + @a + ' FROM #DistinctPages GROUP BY pageno ORDER BY pageno')

Upvotes: 2

Related Questions