Re: Crazy CrossTab problems
 

Here the solution that I got from another forum:

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