sql - TSQL - 将每条记录的行转换为列
问题描述
考虑下表:
+------------------------------------------------------------------------------+
| GUID | DeviceGUID | DetailGUID | sValue | iValue | gValue | DateStored |
| ENTRY1 | DEVICE1 | Detail1 | SN112 | | | 01/01/2020 |
| ENTRY2 | DEVICE1 | Detail4 | | 1241 | | 01/01/2020 |
| ENTRY3 | DEVICE1 | Detail7 | | | GUID12 | 01/01/2020 |
| ENTRY4 | DEVICE2 | Detail1 | SN111 | | | 01/01/2020 |
| ENTRY5 | DEVICE2 | Detail2 | RND123 | | | 01/01/2020 |
| ENRTY6 | DEVICE2 | Detail4 | | 2351 | | 03/01/2020 |
| ENTRY7 | DEVICE3 | Detail1 | SN100 | | | 02/01/2020 |
| [...] | [...] | [...] | | | | |
| | | | | | | |
+------------------------------------------------------------------------------+
我有一个将 DeviceGUID 与 DetailsGUID 链接的表,其想法是为 Details 提供无限选项(只需创建一个新的详细信息,它将是可获取的)。但是,这意味着每个 deviceGUID 的记录数量有限且未知。
我想向我的用户展示的是这样的表格:
+--------+---------------------------------------------------------------------+
| GUID | DeviceGUID |Detail1 |Detail2 |Detail4 |Detail7 |DateStored |
| ENTRY1 | DEVICE1 |SN112 | [NULL] |1241 |GUID12 | [MAX DateStored] |
| ENTRY2 | DEVICE2 |SN111 | RND123 |2351 | [NULL] | [MAX DateStored] |
| ENTRY3 | DEVICE3 |SN100 | | | | |
| [...] | [...] | | | | | |
+------------------------------------------------------------------------------+
我一直在搜索并找到该PIVOT
选项,但似乎只对一个字段起作用,另一个选项是CROSS APPLY
,但(似乎)需要将所有内容转换为相同的数据类型;正如我希望在 ColumnNames 中可见的那样,我将有 3 种类型的数据:String ( VARCHAR
) 值、Integer
值、GUID ( uniqueidentifier
) 值,它们不能互换(意味着具有 GUID Detail1 的 Detail 将始终具有VARCHAR
DetailGUID Detail4永远是一个Integer
到目前为止我能找到的东西:
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns+=N', p.'+QUOTENAME([Name])
FROM
(
SELECT GUID AS [Name]
FROM [dbo].Details AS p
) AS x;
SET @sql = N'
SELECT [DeviceObjectGUID], '+STUFF(@columns, 1, 2, '')+' FROM (
SELECT [DeviceObjectGUID], [DateStored], [DetailGUID] as [Name]
FROM [dbo].[DeviceDetails]) AS j PIVOT (MAX(DateStored) FOR [Name] in
('+STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')+')) AS p;';
EXEC sp_executesql @sql
为转置数据制作动态 PIVOT .. 但如前所述,这仅限于一列
和
select DeviceObjectGUID, value
from DeviceDetails
cross apply
(
select 'sValue', cast(sValue as varchar(MAX)) union all
select 'gValue', cast(gValue as varchar(MAX))union all
select 'iValue', cast(iValue as varchar(MAX))
) c(col, value)
这将使我将所有字段转换为 VARCHAR..
我尝试的另一种选择(理解 PIVOT)是这样的:
SELECT *
FROM
(SELECT *
FROM [dbo].[DeviceDetails]
) AS SourceTable
PIVOT(
max(sValue)FOR [DetailGUID] IN(
[450533BB-43B2-499B-B2F7-094BFAE949B0],
[7483E518-EB61-4B72-93F7-0F97BBFAFA01],
[29B1BDE8-3AD4-4576-8B76-3CAE83E10B11],
[5FC8CC76-12EB-4924-9320-5D09BBE97C10],
[789AA79B-B1DF-4BA2-860A-7129B39D341F],
[C90F4EFE-D848-4BAB-96BF-8DC6BF4F6E62],
[EC6A4ED3-1475-4B0A-8E08-B2F4E095622F],
[D442B7CA-5825-49D9-9977-D88770304B57],
[99B70FEE-999B-4D44-83E9-EB8119B15930],
[3F83ED76-8CC3-4B3D-936A-F528DEB6C045]
)
) AS PivotTable;
('IN
子句中的 GUID 是 DetailGUID)这几乎得到了我想要的,除了它不是动态的并且它仍然限于一个数据列这一事实。( max(sValue)
) 在这种情况下。
====================
作为回应
解决方案
它应该很简单:
SELECT *
FROM
(
SELECT DeviceGUID
,DetailGUID
,CONCAT(sValue, iValue, gValue) as [value]
,DateStored
FROM my_table
) DS
PIVOT
(
MAX([value]) FOR DetailGUID IN (......)
) PVT
推荐阅读
- android - InputTextLayout 需要点击 2 次才能响应 onclick
- android - 为什么 UI 不能正常显示?
- php - Vuejs2 - 如何使用 $(this).val()
- uwp - Windows ML - ONNX - HRESULT 异常:0x88900105
- java - Java JNA - 检测注册表值何时更改
- python - 没有scapy模块的python dns嗅探器
- android - FusedLocationProvider 返回 null
- asp.net-core - Visual Studio 2017 显示 Blazor 错误但编译
- php - 克隆 Laravel 项目并安装 composer 和 npm 然后也给我错误 500 page not found
- pyspark - Apache Zeppelin Spark java.lang.NoSuchMethodError: io/netty/buffer/PooledByteBufAllocator.metric()Lio/netty/buffer/PooledByteBufAllocatorMetric;