CODEWITHSUNDEEP
sql-server-2008pivot-table
Mohemmad K
Mohemmad K

Reputation: 839

How to write select query in "in" clause of pivot table?

Hi I am new to Sql Programming.

I have a table "Temp" having two fields "Name" & "Software"

I am pivoting a table that displays the name and number of versions of software to him.

My query for pivot is : 

select * from temp
pivot(count(Software) for Software in ([Professional],[Personal],[Standard])) as PVT

But instead of giving static value in in ([Professional],[Personal],[Standard])

I want to select the software names from select query that should be : select distinct software from temp

When I write this select query to in clause it gives error.

How do I achieve this?

Please help . Thanks in advance.

Upvotes: 2

Views: 3696

Answers (1)

Mohemmad K
Mohemmad K

Reputation: 839

Declare @cols nvarchar(max)
select @cols = 
stuff( ( select distinct  ',[' + Ltrim(rtrim(Software)) +']' from temp FOR XML PATH('')),1,1,'');

EXEC('select * from temp pivot(count(Software) for Software in ('+@cols+')) as PVT')

The @cols variable will contain the rows fetched from the query select distinct Software from temp as XML format: that is [Standard],[Personal],[Professional] and then the result is sent to the pivot query statement using EXEC() function.

Upvotes: 2

Related Questions