PIVOT Command - 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)

PIVOT Command

Author

Topic

Kickaboo
Starting Member

10 Posts

Posted - 2008-11-30 : 12:40:53

Hi,

I am trying to create a pivot (crosstab) table in SQL 2005, and am usingt he pivot command.

I have managed to get it to pull all the information I want, however, the only problem is, I think I have to hard code every option that appears along the top axis.

SELECT *
FROM Test_ProfileTags
PIVOT
(
Count(EntryID)
FOR TagID
IN ([50003], [50004], [50005], [50006], [50007], [50008], [50009], [50010], [50011])
)
AS p

Is there anyway to have SQL create the top axis automatically? The numbers show at the moment are the ID numbers (although there are 230 at the moment, and that could be added to).

Any suggestions?

Regards

David

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-30 : 12:42:46

for that you need to use dynamic sql using pivot. see below for example

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

Kickaboo
Starting Member

10 Posts

Posted - 2008-11-30 : 13:15:55

Hi,

This works really well, but is there any way of doing it without using store procedures?

Ta

D

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-30 : 23:53:14

no need to create store procedures. just generate the string list in a variable and use it for your pivot

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 01:10:26

something like

DECLARE @TagIDList varchar(max),@Sql varchar(max)
SELECT @TagIDList=LEFT(tl.TagList,LEN(tl.TagList)-1)
FROM (SELECT DISTINCT CAST(TagID AS varchar(10)) + ','
      FROM  Test_ProfileTags
	  ORDER BY TagID
      FOR XML PATH(''))tl(TagList)

SET @Sql='SELECT *
FROM Test_ProfileTags
PIVOT
(
Count(EntryID)
FOR TagID
IN ([' + REPLACE(@TagIDList,',','],[') +'])
) AS p'

EXEC (@Sql)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources