|
发表于 2020-8-30 09:39
来自手机
|
显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
二维转一维,在合并转二维
with 一维表 as (SELECT 序号,联系人,备注,'前腱子' AS 属性,[前腱子] AS [数量] FROM 二维表 WHERE [前腱子] NOT LIKE '' AND 前腱子 NOT LIKE '0' UNION SELECT 序号,联系人,备注,'后腱子' AS 属性,[后腱子] AS [数量] FROM 二维表 WHERE [后腱子] NOT LIKE '' AND 后腱子 NOT LIKE '0' UNION SELECT 序号,联系人,备注,'实际重量' AS 属性,[实际重量] AS [数量] FROM 二维表 WHERE [实际重量] NOT LIKE '' AND 实际重量 NOT LIKE '0' UNION SELECT 序号,联系人,备注,'牛小肉' AS 属性,[牛小肉] AS [数量] FROM 二维表 WHERE [牛小肉] NOT LIKE '' AND 牛小肉 NOT LIKE '0' UNION SELECT 序号,联系人,备注,'上脑' AS 属性,[上脑] AS [数量] FROM 二维表 WHERE [上脑] NOT LIKE '' AND 上脑 NOT LIKE '0' UNION SELECT 序号,联系人,备注,'牛尾肉' AS 属性,[牛尾肉] AS [数量] FROM 二维表 WHERE [牛尾肉] NOT LIKE '' AND 牛尾肉 NOT LIKE '0' UNION SELECT 序号,联系人,备注,'里脊' AS 属性,[里脊] AS [数量] FROM 二维表 WHERE [里脊] NOT LIKE '' AND 里脊 NOT LIKE '0' UNION SELECT 序号,联系人,备注,'胸口' AS 属性,[胸口] AS [数量] FROM 二维表 WHERE [胸口] NOT LIKE '' AND 胸口 NOT LIKE '0' UNION SELECT 序号,联系人,备注,'子盖' AS 属性,[子盖] AS [数量] FROM 二维表 WHERE [子盖] NOT LIKE '' AND 子盖 NOT LIKE '0' UNION SELECT 序号,联系人,备注,'板架' AS 属性,[板架] AS [数量] FROM 二维表 WHERE [板架] NOT LIKE '' AND 板架 NOT LIKE '0' UNION SELECT 序号,联系人,备注,'白板' AS 属性,[白板] AS [数量] FROM 二维表 WHERE [白板] NOT LIKE '' AND 白板 NOT LIKE '0' UNION SELECT 序号,联系人,备注,'眼肉' AS 属性,[眼肉] AS [数量] FROM 二维表 WHERE [眼肉] NOT LIKE '' AND 眼肉 NOT LIKE '0' UNION SELECT 序号,联系人,备注,'牛筋' AS 属性,[牛筋] AS [数量] FROM 二维表 WHERE [牛筋] NOT LIKE '' AND 牛筋 NOT LIKE '0' UNION SELECT 序号,联系人,备注,'牛腩' AS 属性,[牛腩] AS [数量] FROM 二维表 WHERE [牛腩] NOT LIKE '' AND 牛腩 NOT LIKE '0' UNION SELECT 序号,联系人,备注,'肋条' AS 属性,[肋条] AS [数量] FROM 二维表 WHERE [肋条] NOT LIKE '' AND 肋条 NOT LIKE '0')
select 序号,联系人,备注,group_concat(属性||'</td><td>'||数量,'</td><td>') '品名</td><td>数量' from 一维表 group by 联系人 |
-
-
|