TSQL - 将每条记录的行数转换为列数
考虑下表:
+--------------------------------------------------------------------------------+|用户名 |设备GUID |详情GUID |价值 |iValue |gValue |日期存储 ||条目 1 |设备 1 |详情1 |SN112 |||01/01/2020 ||条目2 |设备 1 |详情4 ||第1241章|01/01/2020 ||条目3 |设备 1 |详情7 |||GUID12 |01/01/2020 ||条目4 |设备 2 |详情1 |SN111 |||01/01/2020 ||条目5 |设备 2 |详情2 |RND123 |||01/01/2020 ||ENRTY6 |设备 2 |详情4 ||2351 ||03/01/2020 ||条目7 |设备 3 |详情1 |SN100 |||02/01/2020 ||[...] |[...] |[...] |||||||||||||+------------------------------------------------------------------------------+我有一个表，将 DeviceGUID 与 DetailsGUID 链接起来，其想法是具有无限的详细信息选项(只需创建一个新的详细信息即可获取).但是，这意味着每个 deviceGUID 的记录数量是有限且未知的.
我想向我的用户展示的表格是这样的:
<预><代码>+--------+---------------------------------------------------------------------+|用户名 |DeviceGUID |Detail1 |Detail2 |Detail4 |Detail7 |DateStored ||条目 1 |设备 1 |SN112 |[NULL] |1241 |GUID12 |[最大日期存储] ||条目2 |设备 2 |SN111 |RND123 |2351 |[空] |[最大日期存储] ||条目3 |DEVICE3 |SN100 ||||||[...] |[...] ||||||+------------------------------------------------------------------------------+我一直在搜索并找到了 PIVOT 选项，但它似乎只对一个字段起作用，另一个选项是 CROSS APPLY，但这(似乎)需要将所有内容转换为相同的数据类型；因为我希望在 ColumnNames 中可见，我将有 3 种类型的数据:String (VARCHAR) 值、Integer 值、GUID (uniqueidentifier) 值，并且它们不能互换(意味着具有 GUID Detail1 的 Detail 将始终具有 VARCHAR，具有 DetailGUID Detail4 的 Detail 将始终是 Integer
到目前为止我能发现什么:
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);SET @columns = N'';SELECT @columns+=N', p.'+QUOTENAME([名称])从(选择 GUID 作为 [名称]FROM [dbo].Details AS p) 作为 x;SET @sql = N'SELECT [DeviceObjectGUID], '+STUFF(@columns, 1, 2, '')+' FROM (选择 [DeviceObjectGUID]、[DateStored]、[DetailGUID] 作为 [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.. 但如前所述，这仅限于一列
和
选择 DeviceObjectGUID，值从设备详细信息交叉申请(select 'sValue', cast(sValue as varchar(MAX)) union all选择 'gValue', cast(gValue as varchar(MAX))union all选择 'iValue', cast(iValue as varchar(MAX))) c(列，值)这将使我将所有字段转换为 VARCHAR..
我尝试过的另一个选项(为了理解 PIVOT)是这样的:
SELECT *从(选择 *从 [dbo].[设备详细信息]) AS 源表枢(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])) 作为数据透视表；('IN 子句中的 GUIDS 是 DetailGUID)这几乎让我得到了我想要的，除了它不是动态的，而且它仍然仅限于一个数据列.(max(sValue)) 在这种情况下.
====================
回应
解决方案应该就是这么简单:
SELECT *从(选择设备GUID,详细GUID,CONCAT(sValue, iValue, gValue) as [value],日期存储从 my_table) DS枢(MAX([value]) FOR DetailGUID IN (......)) PVTConsider the following table:
+------------------------------------------------------------------------------+
| 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 |
| [...] | [...] | [...] | | | | |
| | | | | | | |
+------------------------------------------------------------------------------+
I have a table which links a DeviceGUID with a DetailsGUID, with the idea of having unlimited options for Details (just create a new detail and it will be fetchable). however, this means I have a finite and unknown amount of records per deviceGUID.
What I want to show to my users is a table like this:
+--------+---------------------------------------------------------------------+
| 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 | | | | |
| [...] | [...] | | | | | |
+------------------------------------------------------------------------------+
I have been searching a bit and found the PIVOT option but that only seems to function for one field,
another option was CROSS APPLY, but that (seems to) need to convert everything to the same datatype; as I hope is visible n the ColumnNames, I will have 3 types of data: String (VARCHAR) value, Integer value, GUID (uniqueidentifier) value, and they will not be interchangeable (meaning Detail with GUID Detail1 will always have a VARCHAR, Detail with DetailGUID Detail4 will always be an Integer
what I was able to find out until now:
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
to make a dynamic PIVOT for transposing the data.. but as mentioned this is limited to one column
and
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)
This will have me converting all fields to VARCHAR..
One other option I tried (to understand PIVOT) was this:
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;
(The GUIDS in the 'IN clause are the DetailGUIDs)
this almost gets me what I want, except that it is not dynamic and the fact that it is still limited to one data column. (max(sValue)) in this case.
===================
in response to
解决方案It should be as simple as that:
SELECT *
FROM
(
SELECT DeviceGUID
,DetailGUID
,CONCAT(sValue, iValue, gValue) as [value]
,DateStored
FROM my_table
) DS
PIVOT
(
MAX([value]) FOR DetailGUID IN (......)
) PVT
相关文章