题目
有如下一张表T0311
希望得到如下结果:
即对相同的No进行转置
测试数据
CREATE TABLE T0311(
No INT,
NAME NVARCHAR(20),
age INT)
INSERT INTO T0311
SELECT 1,'张三','18'
UNION ALL SELECT 1,'李四','17'
UNION ALL SELECT 1,'王五','23'
UNION ALL SELECT 1,'赵六','40'
UNION ALL SELECT 2,'Tom','17'
UNION ALL SELECT 3,'Bob','19'
UNION ALL SELECT 3,'Tony','36'
UNION ALL SELECT 3,'Petter','25'
参考答案
官方答案:
; WITH Temp AS (
SELECT
[No],
[NAME],
[AGE],
ROW_NUMBER() OVER(
PARTITION BY [No]
ORDER BY
NO ASC
) AS "分组排序"
FROM
[dbo].[T0311]
)
SELECT
[No],
MAX(
CASE 分组排序 WHEN 1 THEN [NAME] ELSE NULL END
) AS NAME1,
MAX(
CASE 分组排序 WHEN 1 THEN [AGE] ELSE NULL END
) AS AGE1,
MAX(
CASE 分组排序 WHEN 2 THEN [NAME] ELSE NULL END
) AS NAME2,
MAX(
CASE 分组排序 WHEN 2 THEN [AGE] ELSE NULL END
) AS AGE2,
MAX(
CASE 分组排序 WHEN 3 THEN [NAME] ELSE NULL END
) AS NAME3,
MAX(
CASE 分组排序 WHEN 3 THEN [AGE] ELSE NULL END
) AS AGE3,
MAX(
CASE 分组排序 WHEN 4 THEN [NAME] ELSE NULL END
) AS NAME4,
MAX(
CASE 分组排序 WHEN 4 THEN [AGE] ELSE NULL END
) AS AGE4
FROM
Temp
GROUP BY
[No]
变通一下:
BEGIN
DECLARE @MaxCol INT,
@i INT=1,
@sql VARCHAR(1000)=''
SELECT @MaxCol=MAX(总数) FROM(SELECT COUNT(1) AS "总数" FROM [dbo].[T0311] GROUP BY [No]) T
WHILE @i<=@MaxCol
BEGIN
SET @sql=@sql+'MAX(CASE 分组排序 WHEN '+CAST(@i AS varchar(1))+ ' THEN [NAME] ELSE NULL END) AS NAME'+CAST(@i AS varchar(1))
+',MAX(CASE 分组排序 WHEN '+CAST(@i AS varchar(1))+' THEN [AGE] ELSE NULL END) AS AGE'+CAST(@i AS varchar(1))+','
SET @i+=1
END
-- 消除最后','
IF LEN(@sql)>0
SET @sql=LEFT(@sql,LEN(@sql)-1)
-- 组装最后的sql查询
SET @sql=';WITH Temp AS (SELECT [No],[NAME],[AGE],ROW_NUMBER() OVER(PARTITION BY [No] ORDER BY NO ASC) AS "分组排序" FROM [dbo].[T0311]) SELECT [No],'+@sql+' FROM Temp GROUP BY [No]'
EXEC(@sql)
END
因为官方写的是固定列
考点: ROW_NUMBER() OVER(PARTITION BY 列 ORDER BY 列)