乾学院 53 号会员
1 回帖  •  45 浏览  • 
     ...

从 SQL 到 SPL：不确定个数但有规则的列转行

 

MSSQL 数据库有一个外部定时生成的不规范的表，每次生成 N 对字段和一条记录，每对字段名都是下划线分隔的两部分，前半部分相同但未知，后半部分分别是固定的 name 和 age。

X1AB_name

X1AB_age

Y2AL_name

Y2AL_age

Z2AL_name

Z2AL_age

Todd

10

Brad

20

Will

30

现在要把每对字段名的前半部分和对应的字段值拼成一条记录，共 N 条记录。可以先把这条记录由列转行，再把每 2 条记录拼成一行。

id

name

age

X1AB

Todd

10

Y2AL

Brad

20

Z2AL

Will

30

SQL 语句：


SELECT V.id,
       MAX(CASE V.subkey WHEN N'name' THEN OJ.value END) AS name,
       MAX(CASE V.subkey WHEN N'age' THEN OJ.value END) AS age
FROM (SELECT *
      FROM dbo.tb YT
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) J(JSON)
     CROSS APPLY OPENJSON (J.JSON) OJ
     CROSS APPLY (VALUES(LEFT(OJ.[key],NULLIF(CHARINDEX('_',OJ.[Key]),0)-1), STUFF(OJ.[Key],1,NULLIF(CHARINDEX('_',OJ.[Key]),0),'')))V(id,subkey)
GROUP BY V.id;

SQL 有 pivot 函数可以进行列转行，但必须写出列名，动态生成列名会非常复杂，这里只能转换思路，先把记录转成 json 串，再分别取多个”字段名: 字段值”，再用叉乘拼成多条记录，代码难度高。SQL 的转置很不灵活，这里改用 max…group by 的方法间接实现，代码也有点啰嗦。

SPL 代码要简单易懂很多：

　A

1

=mssql.query("select * from tb”)

2

=A1.pivot@r(;col,row)

3

=A2.group((#-1)\2)

4

=A3.new(substr@l(~1.col.,"_"):id, ~1.row:name, ~2.row:age)

A1：加载数据。

A2：pivot 函数列转行，不必写出列名，新二维表有 6 条记录 2 个字段，字段 col 存储原字段名，row 存储原字段值。

A3：简单实现每 2 行分一组，而且可以保留分组子集不汇总。# 是行号，\ 是除法取整。

A4：按位置从每组记录取值，形成新二维表。~1 表示组内第 1 条记录。

问题来源：https://stackoverflow.com/questions/78269917/unpivoting-data-in-sql-from-json