sql-server - 将多列转置为来自不同表的行
问题描述
我正在尝试将多列转换为 2 个表中的行,但我不知道如何开始。
我知道如何 UNPIVOT 一张表,只是不知道如何加入它们;如果这是有道理的。
所以我有一个这样的主表:
mainId Date Rd HT AT C1 C2 C3 C4
12 8/9/19 20:00 1 POOL WICH 0 0 2 0
13 8/10/19 12:30 1 HAM MCIT 2 0 2 0
第二个表(通过 mainId 链接到主表):
Id mainId MO_H MO_D MO_A O0.5 ......-> there are like 180 columns; so dynamic SQL is needed here.
331 12 2.09 3.56 4.1 1.063
332 13 1.71 4.15 5.65 1.048
我想要实现的是:
mainId Date Rd HT AT Column1 Column2
12 8/9/19 20:00 1 POOL WICH MO_H 2.09
12 8/9/19 20:00 1 POOL WICH MO_D 3.56
12 8/9/19 20:00 1 POOL WICH MO_A 4.1
12 8/9/19 20:00 1 POOL WICH O0.5 1.063
13 8/10/19 12:30 1 HAM MCIT MO_H 1.71
13 8/10/19 12:30 1 HAM MCIT MO_D 4.15
13 8/11/19 12:30 1 HAM MCIT MO_A 5.65
13 8/12/19 12:30 1 HAM MCIT O0.5 1.048
提前致谢。
解决方案
这是一个选项,可以在不实际使用动态 SQL 的情况下动态取消数据透视。这种方法是 XML ... JSON 版本不喜欢您的列名[O0.5]
请注意,您只需要排除某些列 ... not in ('Id','mainId')
我还应该补充一点:NULL 值将被排除在外。
例子
Select A.[mainId]
,A.[Date]
,A.[Rd]
,A.[HT]
,A.[AT]
,D.*
From Main A
Join Second B on A.mainId=B.mainId
Cross Apply ( values ( convert(xml,(Select B.* for XML RAW)) ) ) C(XMLData)
Cross Apply (
Select Item = xAttr.value('local-name(.)', 'varchar(100)')
,Value = xAttr.value('.','varchar(max)') --<< use proper data type
From XMLData.nodes('//@*') xNode(xAttr)
Where xAttr.value('local-name(.)', 'varchar(100)') not in ('Id','mainId')
) D
退货
mainId Date Rd HT AT Item Value
12 8/9/19 20:00 1 POOL WICH MO_H 2.09
12 8/9/19 20:00 1 POOL WICH MO_D 3.56
12 8/9/19 20:00 1 POOL WICH MO_A 4.10
12 8/9/19 20:00 1 POOL WICH O.05 1.063
13 8/10/19 12:30 1 HAM MCIT MO_H 1.71
13 8/10/19 12:30 1 HAM MCIT MO_D 4.15
13 8/10/19 12:30 1 HAM MCIT MO_A 5.65
13 8/10/19 12:30 1 HAM MCIT O.05 1.048
推荐阅读
- java - Java Spring Boot:项目将图像保存在错误的路径中,使用IDE Intellji打开时不显示图像
- python - Python - Pandas Dataframe 以正确的方式合并两个数据框
- xamarin - ExcelDataReader 1252 编码问题
- android - kotlin firebase auth 在登录期间崩溃
- python - 如何为自定义函数创建单独的类?
- sql-server - SQL Server 2019:选择刚刚插入的行
- android - Android:重度recyclerview位图
- string - C#如何将唯一字符串传递给异步方法?
- ios - 如何阻止 iOS 应用在应用打开时请求位置服务权限?
- javascript - 如何在 nodejs 中捕获 GotError [HTTPError]: Response code 404 (Not Found)