using pivot - 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)

using pivot

Author

Topic

cognos79
Posting Yak Master

241 Posts

Posted - 2007-06-15 : 19:52:25

I am using this example to understand pivot functionality.

declare @test table (BatchID int ,Status int)

insert into @test select 1001 ,1
insert into @test select 1001 ,2
insert into @test select 1002 ,0
insert into @test select 1002 ,3
insert into @test select 1002 ,4
insert into @test select 1003 ,4
insert into @test select 1004 ,4

this is the query:
SELECT *
FROM
(SELECT BatchId,status from @test) p
PIVOT
( count(Status) for status in ([1],[2],[3],[4]))
AS pvt
ORDER BY BatchId

Could somebody tell me if i can make the query dynamic instead of hard coding the values ([1],[2],[3],[4]) in IN clause something like "( count(Status) for status in (select distinct status from @test)). ".

-- Thanks

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-06-15 : 22:22:09

This article shows a easy way to do this.

http://blog.crowe.co.nz/archive/2005/09/05/250.aspx

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-16 : 00:07:03

quote:
Originally posted by Vinnie881

This article shows a easy way to do this.

http://blog.crowe.co.nz/archive/2005/09/05/250.aspx

Thats modified version of SQLTeam's article which is specifically for SQL Server 2000. As 2005 supports PIVOT, do google search on Dynamic PIVOT+sql server 2005

Madhivanan

Failing to plan is Planning to fail

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-06-16 : 00:56:16

Madhivanan was correct. Here's a way to do it in 2005


Create table #Test(BatchID int ,Status int)

insert into #test select 1001 ,1
insert into #test select 1001 ,2
insert into #test select 1002 ,0
insert into #test select 1002 ,3
insert into #test select 1002 ,4
insert into #test select 1003 ,4
insert into #test select 1004 ,4


declare @Status varchar(50)
set @status = ''

Select @Status = @Status + '[' + convert(varchar(2),a.status )+ '],' from (Select distinct a.Status from #Test a) a

SET @Status = LEFT(@Status, LEN(@Status) - 1)

exec('SELECT * FROM #test p PIVOT( count(Status) for status in (' + @Status + ')) AS pvt ORDER BY BatchId')
drop table #test

cognos79
Posting Yak Master

241 Posts

Posted - 2007-06-16 : 01:21:46

Thanks a lot guys...It works!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources