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)

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值