Re: Simple SQL Question
Hi Peter,
Thank you, but I'm having a hard time relating the examples to my specific situation. However, I did find where someone accomplished the same task in SQL Server. I'm wondering how to do the following in MySQL:
-- DROP TABLE #MyTable
-- CREATE TABLE #MyTable( name varchar(10), ID int, value int );
INSERT #MyTable VALUES ('Sam', 1, 15);
INSERT #MyTable VALUES ('Sam', 2, 6);
INSERT #MyTable VALUES ('Bob', 1, 9);
INSERT #MyTable VALUES ('Bob', 2, 11);
-- INSERT #MyTable VALUES ('Sam', 3, 1);
-- INSERT #MyTable VALUES ('Bob', 3, 2);
DECLARE @cols NVARCHAR(2000)
DECLARE @query NVARCHAR(4000)
SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
'],[' + CONVERT(varchar(5), t.ID )
FROM #MyTable AS t
--ORDER BY '],[' + t.ID
FOR XML PATH('')
), 1, 2, '') + ']'
--SELECT @cols
SET @query = N'SELECT name,'+ @cols +' FROM
(SELECT t1.name, t1.ID, t1.Value FROM #MyTable AS t1) p
PIVOT (MAX([Value]) FOR ID IN ( '+ @cols +' ))
AS pvt;'
EXECUTE(@query)