MsSql 行列转化

行列转化 

USE MyDataBase
GO 

CREATE TABLE UserInfo(id INT,NAME VARCHAR(50))
INSERT INTO userinfo VALUES(1,'ua')
INSERT INTO userinfo VALUES(2,'ub')
INSERT INTO userinfo VALUES(3,'uc')

CREATE TABLE Hobby(id INT,NAME VARCHAR(50))
INSERT INTO Hobby VALUES(1,'ha')
INSERT INTO Hobby VALUES(2,'hb')
INSERT INTO Hobby VALUES(3,'hc')

CREATE TABLE UserHobby(uid INT ,hid INT)
INSERT INTO UserHobby VALUES(1,1)
INSERT INTO UserHobby VALUES(1,2)
INSERT INTO UserHobby VALUES(2,3)
INSERT INTO UserHobby VALUES(2,2)

SELECT * FROM UserInfo
SELECT * FROM Hobby
SELECT * FROM UserHobby

DECLARE @col VARCHAR(8000)
SELECT @col= isnull(@col + '],[' , '') +  NAME  FROM dbo.Hobby
SET @col='['+@col+']'

PRINT @col 

EXEC('
SELECT id,uname,ISNULL(ha,''无''),ISNULL(hb,''无''),ISNULL(hc,''无'') FROM(
SELECT u.id,u.NAME AS uname,h.NAME AS hname FROM  dbo.UserInfo u INNER JOIN dbo.UserHobby uh ON u.id=uh.uid
INNER JOIN dbo.Hobby h ON uh.hid=h.id
)a
PIVOT (
MAX(hname)FOR  hname IN ('+@col+')
) b
'
)