MySQL Forums
Forum List  »  Newbie

Re: Simple SQL Question
Posted by: Nicholas Taylor
Date: August 27, 2017 11:59AM

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)

Options: ReplyQuote


Subject
Written By
Posted
August 26, 2017 11:56PM
August 27, 2017 08:50AM
Re: Simple SQL Question
August 27, 2017 11:59AM
August 27, 2017 05:00PM
August 27, 2017 06:21PM
August 27, 2017 06:38PM
August 27, 2017 07:14PM
August 27, 2017 08:20PM
September 07, 2017 02:38AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.