行列转置

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
	id INT IDENTITY(1,1) PRIMARY KEY,
	buyer NVARCHAR(10),
	goods NVARCHAR(10)
)
GO
INSERT INTO t VALUES ('A商','橘子')
INSERT INTO t VALUES ('B商','西瓜')
INSERT INTO t VALUES ('A商','葡萄')
INSERT INTO t VALUES ('B商','哈密瓜')
INSERT INTO t VALUES ('B商','橘子')
INSERT INTO t VALUES ('C商','山竹')
INSERT INTO t VALUES ('A商','芒果')
INSERT INTO t VALUES ('A商','香蕉')
GO
;WITH cte AS(
	SELECT ROW_NUMBER() OVER (PARTITION BY buyer ORDER BY id) AS rid,* FROM t
)
SELECT buyer,(
	SELECT goods FROM cte WHERE cte.buyer=t.buyer AND rid=1 	
) AS [商品1]
,(
	SELECT goods FROM cte WHERE cte.buyer=t.buyer AND rid=2	
) AS [商品2]
,(
	SELECT goods FROM cte WHERE cte.buyer=t.buyer AND rid=3 	
) AS [商品3]
,(
	SELECT goods FROM cte WHERE cte.buyer=t.buyer AND rid=4	
) AS [商品4]
FROM t GROUP BY buyer