Tada...
-- Copy Article stuff
DECLARE @List VARCHAR(1000), @Sql VarChar(5000)
SET @List = ''
SET @Sql = ''
IF EXISTS (SELECT * FROM tempdb.sys.objects
WHERE name LIKE '#tblX%' AND type IN (N'U'))
DROP TABLE [dbo].#tblX
CREATE TABLE #tblX(MixDate DateTime, NoMixes Int, MixPlant_Name VarChar(50) )
INSERT [#tblX]
SELECT DISTINCT(mp.MixDate), SUM(mp.NoMixes) As NoMixes, Mixplant.MixPlant_Name
FROM MixProduction mp
INNER JOIN MixPlant
ON mp.MixPlant_ID = Mixplant.ID And MixPlant.IsActive =1
WHERE mp.MixDate BETWEEN '2/1/2011' AND '2/28/2011'
Group By Mixplant.MixPlant_Name, mp.MixDate
DECLARE @tblY TABLE(MixPlant_Name VarChar(50))
INSERT @tblY
SELECT DISTINCT MixPlant_Name
FROM #tblX
ORDER BY MixPlant_Name
SELECT @List = ISNULL(@List, '') +
CASE WHEN ISNULL(@List,'') = ''
THEN '[' + MixPlant_Name + ']'
ELSE ',[' + MixPlant_Name + ']' END
FROM @tblY
ORDER BY MixPlant_Name
SET @Sql = 'SELECT MixDate, ' + @List + CHAR(13)
SET @Sql = @Sql + 'FROM (SELECT MixDate, NoMixes, MixPlant_Name ' + CHAR(13)
SET @Sql = @Sql + 'FROM #tblX ) As P ' + CHAR(13)
SET @Sql = @Sql + 'PIVOT (SUM(NoMixes) FOR MixPlant_Name IN (' + @List + ')) As Pvt' + CHAR(13)
-- test output
--print @sql
--SELECT * FROM #tblX
-- exucute dynamic sql
Exec (@Sql)
-- Drop Table
DROP TABLE #tblX
GO
Thanks for prompting me to persist. Now to add a couple more columns to the pivot :-D
I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife
