SQL每日一题--20210311

题目

有如下一张表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 列)

参考:SQL每日一题(20210311)