Pivot?

[Andy_L_J]

I always struggle when it comes to PIVOTs (as I am pretty sure that is what is required). I have the following query:

WITH plant As
(SELECT mp.ID, mp.MixPlant_Name
FROM MixPlant mp
WHERE mp.IsActive = 1)
SELECT DISTINCT(mp.MixDate), plant.MixPlant_Name, SUM(mp.NoMixes) As No
FROM MixProduction mp
JOIN plant
ON mp.MixPlant_ID = plant.ID
WHERE mp.MixDate BETWEEN '2/22/2011' AND '2/23/2011'
Group By plant.MixPlant_Name, mp.MixDate
Go

which produces the following result:

MixDate MixPlant_Name No
2011-02-22 00:00:00.000 MTI 88
2011-02-22 00:00:00.000 Plasmec 116
2011-02-22 00:00:00.000 Prodex 27
2011-02-23 00:00:00.000 MTI 69
2011-02-23 00:00:00.000 Plasmec 121
2011-02-23 00:00:00.000 Prodex 15

I am looking to get the following output:

MixDate MTI Plasmec Prodex
2011-02-22 00:00:00.000 88 116 27
2011-02-23 00:00:00.000 36 121 15

Any suggestions, links or gentle sarcasm appreciated.

I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife

[Mycroft Holmes]

Some have found this article[^] useful, I know you need just the standard 1 column pivot but anyone who can use a CTE should have no problems with this.

Never underestimate the power of human stupidity RAH

[Andy_L_J]

Thanks, I had actually looked at that is my wanderings. I will post my solution when complete.

I don't speak Idiot - please talk slowly and clearly 'This space for rent' Driven to the arms of Heineken by the wife

[Andy_L_J]

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