2

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.

Mohemmad K
  • 809
  • 7
  • 33
  • 74
  • The list has to be a predefined list of static items. Imagine the list would be a dynamic list: the column names would be unknown at compile time of the query. How should the parser check the syntax? – Claude Mar 13 '13 at 07:29
  • Sir, I searched over internet and I found to store the query in a variable and pass that variable to `in` clause. But actually I don't know how to implement this.? @Michael – Mohemmad K Mar 13 '13 at 07:41
  • 2
    In this case you have to build your whole query dymanically and you have to execute it using `exec`. – Claude Mar 13 '13 at 07:55
  • Can you help me how to do so? or provide the links for proper tutorial for these? @Michael – Mohemmad K Mar 13 '13 at 08:00
  • see [documentation](http://msdn.microsoft.com/en-us/library/ms188332%28v=sql.100%29.aspx) and examples provided with the docs – Claude Mar 13 '13 at 08:05

1 Answers1

2
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.

Mohemmad K
  • 809
  • 7
  • 33
  • 74