Crosstab Help - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Crosstab Help

Author

Topic

omega1983
Starting Member

40 Posts

Posted - 2009-11-25 : 21:49:00

I have the following temp variable table

DECLARE @ID TABLE
(

corekey numeric(13),
coreid char(10),
coretype char (10),
corecomm varchar(4000)
)

INSERT INTO @ID (corekey,coreid,corecomm)
SELECT corekey,coreid,corecomm
FROM dbo.core_table

Here is sample output
corekey coreid corecomm
1233 1111 northeast
1234 1111 southeast
1235 1233 west
1236 1254 west
One ID can have many corekeys. Each corekey commands its own detail line (see coreid 1111) I want the data to read this way
coreid corecomm1 corecomm2 corecomm3
1111 northeast southeast
1235 west
1236 west

I cannot hardcode the corecomm because there are too many of them. I need a dynamic crosstab or something similar. I am using SQL 2005. Can I accomplish this without having to write a complex cursor

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-26 : 01:43:43

See if this helps
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

ranganath
Posting Yak Master

209 Posts

Posted - 2009-11-27 : 01:54:01

Hi try with this

DROP TABLE #Id

CREATE TABLE #Id ( corekey numeric(13), coreid char(10), corecomm varchar(4000) )
INSERT INTO #Id (corekey,coreid,corecomm)
SELECT 1233, 1111, 'northeast' UNION ALL
SELECT 1234, 1111, 'southeast' UNION ALL
SELECT 1232, 1111, 'sotheast' UNION ALL
SELECT 1235, 1233, 'west' UNION ALL
SELECT 1236, 1254, 'west'

Declare @Sql varchar(Max), @Str Varchar(Max)
Set @Sql = ''
Set @Str = ''

Select @Str = @Str + ', Min(Case when Row = ''' +cast(Row as varchar(128))+ '''
then corecomm End ) AS " corecomma '+ CAST(Row AS VARCHAR(128))+'"'
From (
Select DISTINCT ROW_NUMBER() OVER(PARTITION BY coreid ORDER BY corekey) AS Row From #Id
)a

Select @Sql = @Sql + 'Select DISTINCT
CASE WHEN coreidcnt > 1 THEN coreid ELSE corekey END AS corekey
'+(@Str)+'
From (Select *, ROW_NUMBER() OVER(PARTITION BY coreid ORDER BY corekey) AS ''Row''
, COUNT(corekey) OVER(PARTITION BY coreid) AS ''coreidcnt''
From #Id ) A
group by (CASE WHEN coreidcnt > 1 THEN coreid ELSE corekey END) '

print @Sql
Exec (@Sql)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources