Convert temp variable table to crosstab

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)

Convert temp variable table to crosstab

Author

Topic

omega1983
Starting Member

40 Posts

Posted - 2009-11-24 : 11:27:00

DECLARE @AttrHobby TABLE
(
attrkey numeric(13),
attrid char(10),
attrtype char(6),
attrtypenm varchar(65),
attrcomm varchar(4000)
)

INSERT INTO @AttrHobby (attrkey,attrid,attrtype,attrtypenm,attrcomm)
SELECT attrkey,attrid,attrtype,attrtypenm,attrcomm
FROM dbo.attribute
where attrtype = 'hobby'
and attrid = '0000006313'
order by attrtypenm
This temp variable table produces the following output
attrkey attrid attrype attrtypenm attrcomm
1234 0000006313 hobby hobby reading
1235 0000006313 hobby hobby automobiles

I know want to convert this to a crosstab, with data looking like this
attrid attrype attrcomm1 attrcomm2
0000006313 hobby reading automobiles

I tried a max case statement but it lists the data vertically. This is because each attrkey produces a new line of data

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-24 : 11:32:42

You can search for PIVOT, maybe that helps.

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-25 : 01:50:23

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

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources