dynamic pivot table

hi

i am trying to create a dynamic sql statement so i can pivot information...

my problem is that when i run this query i get the error information below. i am running sql server 2000... can this run in 2005?

this runs perfectly when only one record is returned in the sub query...

declare @.query varchar(300)
select @.query = 'Select '+ char(10) + (
select Code
+'case when Code = '''+ Code +''' then count(Code) else 0 end as '+Code+''+char(10)
as [text()]

from WipAvailable WA
)+ char(10) +
' from WipMaster group by Code'

select @.query
exec (@.query)

Server: Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

(1 row(s) affected)

u have given the answer..."this runs perfectly when only one record is returned in the sub query...", in this case the outer query just expects one result to be returned by the subquery... and it'll give the same error in 2005 as well... u need to look at the logic...

i dont know ur exact aim..if pivot is the aim , there is a pivot operator u can use in sql server 2005, to make row data into columns...

|||

if you are using the sql server 2005 then use Pivot operator,

example,

SELECT
[1] as [Code-1],
[2] as [Code-2],
[3] as [Code-3]
FROM
(SELECT Code from WipAvailable) Master
PIVOT
(Count(Code) for Code in ([1],[2],[3]))
AS pvt

to achive the above query dynamically use the following statement,

Declare @.Columns as varchar(1000);
Declare @.Values as varchar(1000);
Declare @.Query as varchar(3000);
select
@.Columns = Isnull(@.Columns,'') + '[' + Code + '] as [Code-' + Code + '],' ,
@.Values = Isnull(@.Values,'') + '[' + Code + '],'
From WipMaster

select @.Columns = Substring(@.Columns,1,Len(@.Columns)-1),
@.Values = Substring(@.Values,1,Len(@.Values)-1)

select @.Query = 'Select ' + @.Columns + ' From (Select Code From WipAvailable) Master'
+ ' Pivot (Count(Code) for Code in (' + @.values + ')) as Pvt'

Exec (@.Query)

|||

To work on SQL Server 2000,

Use the following query

Select
(Select count(Code) from WipAvailable where Code=1) as [1]
,(Select count(Code) from WipAvailable where Code=2) as [2]
,(Select count(Code) from WipAvailable where Code=3) as [3]

To achive the above query dynamically use the following statement

Declare @.query varchar(8000)
select @.query = Isnull(@.query,'') + '(Select count(Code) '
+ char(10) +
' from WipAvailable where Code=' + Code + ') as [' + Code + '],'
from Wipmaster Master
Order By Code
select @.query = 'Select ' + Substring(@.query,1,Len(@.query)-1)
Exec (@.query)