午夜免费高清视频,精品亚洲一区二区三区,蜜桃视频在线观看www社区

本文介紹了TSQL - 將每條記錄的行數(shù)轉換為列數(shù)的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學習吧！

問題描述

考慮下表:

+--------------------------------------------------------------------------------+|用戶名 |設備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 鏈接起來，其想法是具有無限的詳細信息選項(只需創(chuàng)建一個新的詳細信息即可獲取).但是，這意味著每個 deviceGUID 的記錄數(shù)量是有限且未知的.

我想向我的用戶展示的表格是這樣的:

<預><代碼>+--------+---------------------------------------------------------------------+|用戶名 |DeviceGUID |Detail1 |Detail2 |Detail4 |Detail7 |DateStored ||條目 1 |設備 1 |SN112 |[NULL] |1241 |GUID12 |[最大日期存儲] ||條目2 |設備 2 |SN111 |RND123 |2351 |[空] |[最大日期存儲] ||條目3 |DEVICE3 |SN100 ||||||[...] |[...] ||||||+------------------------------------------------------------------------------+

我一直在搜索并找到了 PIVOT 選項，但它似乎只對一個字段起作用，另一個選項是 CROSS APPLY，但這(似乎)需要將所有內容轉換為相同的數(shù)據(jù)類型；因為我希望在 ColumnNames 中可見，我將有 3 種類型的數(shù)據(jù):String (VARCHAR) 值、Integer 值、GUID (uniqueidentifier) 值，并且它們不能互換(意味著具有 GUID Detail1 的 Detail 將始終具有 VARCHAR，具有 DetailGUID Detail4 的 Detail 將始終是 Integer

到目前為止我能發(fā)現(xiàn)什么:

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

制作用于轉置數(shù)據(jù)的動態(tài) 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])) 作為數(shù)據(jù)透視表；

('IN 子句中的 GUIDS 是 DetailGUID)這幾乎讓我得到了我想要的，除了它不是動態(tài)的，而且它仍然僅限于一個數(shù)據(jù)列.(max(sValue)) 在這種情況下.

====================

回應

解決方案

應該就是這么簡單:

SELECT *從(選擇設備GUID,詳細GUID,CONCAT(sValue, iValue, gValue) as [value],日期存儲從 my_table) DS樞(MAX([value]) FOR DetailGUID IN (......)) PVT

Consider 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

這篇關于TSQL - 將每條記錄的行數(shù)轉換為列數(shù)的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！

【網(wǎng)站聲明】本站部分內容來源于互聯(lián)網(wǎng),旨在幫助大家更快的解決問題，如果有圖片或者內容侵犯了您的權益，請聯(lián)系我們刪除處理，感謝您的支持！

久久久久久久av_日韩在线中文_看一级毛片视频_日本精品二区_成人深夜福利视频_武道仙尊动漫在线观看

TSQL - 將每條記錄的行數(shù)轉換為列數(shù)

問題描述

相關文檔推薦