PIVOT TABLE query !!

hi ,

is it possible to do a pivot , where the number of columns is dynamic...i.e

i dont know how many rows will be selected , and i want to pivot them and insert into

a new (temp/tabletype)table...obv i dont know how many columns i need....

somethin like the example of books online pasted below , consider here that i need data for

all employees (distinct empid) , then pivot it, for that i'll need 'select distinct empid

from emp' in the pivot syntax 'FOR EmployeeID IN' .

pls tell me if such thing is possible or there is a turnaround for my problem...

SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID

you may try this (not sure for the perfs) :

declare @.sql1 as varchar(2000), @.sql2 as varchar(2000), @.sql3 as varchar(2000), @.empid as int

SET @.sql1 = 'SELECT VendorID '

SET @.sql2 = 'FROM (SELECT PurchaseOrderID, EmployeeID, VendorID FROM Purchasing.PurchaseOrderHeader) p '
SET @.sql2 = @.sql2 + 'PIVOT (COUNT (PurchaseOrderID) FOR EmployeeID IN ( '

SET @.sql3 = ') ) AS pvt ORDER BY VendorID'

DECLARE emp_cur CURSOR FAST_FORWARD FOR
SELECT DISTINCT EmployeeID FROM Purchasing.PurchaseOrderHeader

open emp_cur

fetch next from emp_cur into @.empid
while @.@.fetch_status = 0
begin
set @.sql1 = @.sql1 + ',' + cast(empid as varchar(10))
set @.sql2 = @.sql3 + cast(empid as varchar(10)) + ','
fetch next from emp_cur into @.empid
end

close emp_cur
deallocate emp_cur

set @.sql2 = LEFT(@.sql2, LEN(@.sql2)-1)

print @.sql1 + @.sql2 + @.sql3 -- For debug
exec (@.sql1 + @.sql2 + @.sql3)

|||

You have to create dynamic SQL and then execute it, the PIVOT statement does not support dynamic column lists itself.

You can get the list of columns by creating a variable and populating it like this

DECLARE @.pivotColumns nvarchar(2000), @.sql nvarchar(4000)
SET @.pivotColumns = ''
SELECT @.pivotColumns = @.pivotColumns + '[' + cast(EmployeeID AS nvarchar(10)) + '],'
FROM (SELECT distinct EmployeeID FROM Purchasing.PurchaseOrderHeader) p
SET @.pivotColumns = LEFT(@.pivotColumns, LEN(@.pivotColumns) - 1)
SET @.sql = 'SELECT *
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( ' + @.pivotColumns + ' )
) AS pvt
ORDER BY VendorID'
EXEC (@.sql)

There is a very nice article describing this here

http://www.theabstractionpoint.com/dynamiccolumns.asp

|||

Hello:

Could you check out this thread to see whether you can figure out something?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=871809&SiteID=1

|||

thanks a lot buddy...actually i have a bit more tweek in my problem....i need the resultant recordset in a temp table.... as i dont know many columns will be in it , select into has to be used..now when i use it in buliding my query string and then execute it (@.sql) , later select * from temp , it ives an error... invalid object name '#temp' ...

(SELECT PurchaseOrderID, EmployeeID, VendorID
into #temp

FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( ' + @.pivotColumns + ' )
) AS pvt
ORDER BY VendorID'
EXEC (@.sql)

select * from #temp

-->some more help....

|||

The problem is that local temporary tables (with a # at the beginning of the name) are very local - so they are gone after the dynamic SQL finishes executing.

You could create a global temporary table (with two ## at the beginning of the name). The problem is that then the temp table will be available to all connections, so if it is possible that this code will ever run on two connections at the same time that won't work. So now you have to get tricky, you create the #temp table first (with the known VendorID column, but none of the other columns, because they are not known). Then you alter the table in the dynamic code before you insert.

DROP TABLE #temp
CREATE TABLE #temp(VendorID int)
DECLARE @.pivotColumns nvarchar(2000), @.alterColumns nvarchar(2000), @.sql nvarchar(4000)
SET @.pivotColumns = ''
SET @.alterColumns = ''
SELECT @.pivotColumns = @.pivotColumns + '[' + cast(EmployeeID AS nvarchar(10)) + '],',
@.alterColumns = @.alterColumns + '[' + cast(EmployeeID AS nvarchar(10)) + '] int,'
FROM (SELECT distinct EmployeeID FROM Purchasing.PurchaseOrderHeader) p
SET @.pivotColumns = LEFT(@.pivotColumns, LEN(@.pivotColumns) - 1)
SET @.alterColumns = LEFT(@.alterColumns, LEN(@.alterColumns) - 1)
SET @.sql = 'ALTER TABLE #temp
ADD ' + @.alterColumns
EXEC(@.sql)
SET @.sql = 'INSERT #temp
SELECT *
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( ' + @.pivotColumns + ' )
) AS pvt
ORDER BY VendorID'
EXEC (@.sql)

SELECT * FROM #temp

|||thanka a lot buddy....works perfect for me..