MS SQL

Crazy CrossTab problems

Please support our MS SQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Dec 2008

Posts: 9

Reputation: muek is an unknown quantity at this point

Solved Threads: 0

muek

Offline

Newbie Poster

Re: Crazy CrossTab problems

#11

Jan 1st, 2009

Here the solution that I got from another forum:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
SELECT ROW_NUMBER() OVER (PARTITION BY workID ORDER BY machineID) AS Seq,* INTO #Temp
FROM TABLE
 
DECLARE @MachineList varchar(1000),@Sql varchar(max)
 
SELECT @MachineList= LEFT(ml.MacList,LEN(ml.MacList)-1)
FROM (SELECT DISTINCT CAST(Seq AS varchar(10)) + ','
      FROM #Temp
      FOR XML PATH('')
     )ml(MacList)
 
SET @Sql='SELECT *
FROM (SELECT *
FROM #Temp
)t
PIVOT (SUM(hours) FOR Seq IN (['+ REPLACE(@MachineList,',','],[') + ']))p'
EXEC(@Sql)
DROP TABLE #Temp
SELECT ROW_NUMBER() OVER (PARTITION BY workID ORDER BY machineID) AS Seq,* INTO #Temp
FROM Table

DECLARE @MachineList varchar(1000),@Sql varchar(max)

SELECT @MachineList= LEFT(ml.MacList,LEN(ml.MacList)-1)
FROM (SELECT DISTINCT CAST(Seq AS varchar(10)) + ','
      FROM #Temp
      FOR XML PATH('')
     )ml(MacList)

SET @Sql='SELECT *
FROM (SELECT *
FROM #Temp
)t
PIVOT (SUM(hours) FOR Seq IN (['+ REPLACE(@MachineList,',','],[') + ']))p'
EXEC(@Sql)
DROP TABLE #Temp