SQL SERVER特殊行转列案列一则
2015-02-11 14:01 潇湘隐者 阅读(1379) 评论(2) 编辑 收藏 举报今天有个同事找我,他说他有个需求,需要进行行转列,但是又跟一般的行转列有些区别,具体需求如下所说,需要将表1的数据转换为表2的显示格式.
我想了一下,给出了一个解决方法,具体如下所示(先给出测试数据)
INSERT INTO TEST
SELECT 1, 1, '定型名称', '预定型' UNION ALL
SELECT 1, 2, '进布方式', '调平' UNION ALL
SELECT 1, 3, '21米长定型机开机速度', '25' UNION ALL
SELECT 1, 4, '烘箱温度', '195' UNION ALL
SELECT 1, 5, '门幅(CM)', '200-210-210' UNION ALL
SELECT 2, 1, '过软', 'na' UNION ALL
SELECT 2, 2, '调平', 'na' UNION ALL
SELECT 2, 3, '25', '+/-0.5' UNION ALL
SELECT 2, 4, '150', '+/-5℃头尾烘箱除外' UNION ALL
SELECT 2, 5, '188-198-198', '+/-3'
实现其功能的SQL语句如下所示
WITH T
AS
(
SELECT NO, ROW_NUMBER() OVER (PARTITION BY NO ORDER BY STEP) AS ROWID, NAME, VAlUE FROM TEST
)
SELECT NO, MAX(NAME) AS NAME, MAX(VALUE) AS VALUE, MAX(NAME2) AS NAME2 , MAX(VALUE2) AS VALUE2
FROM
(
SELECT NO, NAME AS NAME, VALUE AS VALUE, NULL AS NAME2, NULL AS VALUE2 FROM T WHERE ROWID =1
UNION ALL
SELECT NO, NULL AS NAME ,NULL AS VALUE, NAME AS NAME2, VALUE AS VALUE2 FROM T WHERE ROWID =2
) TT
GROUP BY NO
但是这样有一个弊端就是同一NO的记录不定(不知道有多少条记录),那么上面SQL语句就不知道怎么写了,好在这个需求每个NO最多只有四条记录,所以可以写成下面. 如果记录数再多的话,这个SQL语句就写的很纠结。暂时也没有想到更好的解决方法。
WITH T
AS
(
SELECT NO, ROW_NUMBER() OVER (PARTITION BY NO ORDER BY STEP) AS ROWID, NAME, VAlUE FROM TEST
)
SELECT NO, MAX(NAME) AS NAME , MAX(VALUE) AS VALUE
, MAX(NAME2) AS NAME2 , MAX(VALUE2) AS VALUE2
, MAX(NAME3) AS NAME3 , MAX(VALUE3) AS VALUE3
, MAX(NAME4) AS NAME4 , MAX(VALUE4) AS VALUE4
FROM
(
SELECT NO, NAME AS NAME , VALUE AS VALUE ,
NULL AS NAME2, NULL AS VALUE2 ,
NULL AS NAME3, NULL AS VALUE3 ,
NULL AS NAME4, NULL AS VALUE4
FROM T WHERE ROWID =1
UNION ALL
SELECT NO, NULL AS NAME , NULL AS VALUE ,
NAME AS NAME2, VALUE AS VALUE2 ,
NULL AS NAME3, NULL AS VALUE3 ,
NULL AS NAME3, NULL AS VALUE4
FROM T WHERE ROWID =2
UNION ALL
SELECT NO, NULL AS NAME , NULL AS VALUE ,
NULL AS NAME2, NULL AS VALUE2 ,
NAME AS NAME3, VALUE AS VALUE3 ,
NULL AS NAME4, NULL AS VALUE4
FROM T WHERE ROWID =3
UNION ALL
SELECT NO, NULL AS NAME , NULL AS VALUE ,
NULL AS NAME2, NULL AS VALUE2 ,
NULL AS NAME3, NULL AS VALUE3 ,
NAME AS NAME4, VALUE AS VALUE4
FROM T WHERE ROWID =4
) TT
GROUP BY NO

【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
· DeepSeek 解答了困扰我五年的技术问题
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· 易语言 —— 开山篇
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比