[PlayerCode] | [Name] | [Age] | [Club]
142 | Messi | 34 | PSG
333 | Ronaldo | 36 | Manchester United
532 | Pique | 34 | FC Barcelona
[PlayerCode] | [Key] | [Value]
142 | Name | Messi
142 | Age | 34
142 | Club | PSG
333 | Name | Ronaldo
333 | Age | 36
333 | Club | Manchester United
532 | Name | Pique
532 | Age | 34
532 | Club | FC Barcelona
我想使用 SQL Server 將第一個表轉換為第二個鍵/值表。我假設 'unpivot' 函式將用于進行轉換，但我不知道如何..
有誰知道如何做到這一點？
uj5u.com熱心網友回復：
我看到有更多答案，但我將答案留在這里，因為它是唯一使用 UNPIVOT 的答案。也許它對您或任何其他人有用：
create table players (
[PlayerCode] int,
[Name] varchar(50),
[Age] int,
[Club] varchar(50)
)
insert into players values
(142, 'Messi', 34, 'PSG'),
(333, 'Ronaldo', 36, 'Manchester United'),
(532, 'Pique', 34, 'FC Barcelona')
SELECT PlayerCode, [Key], Value
FROM
(SELECT PlayerCode, Name, cast(Age as varchar(50)) Age, Club
FROM players) p
UNPIVOT
(Value FOR [Key] IN
(Name, Age, Club)
)AS unpvt;
uj5u.com熱心網友回復：
您可以使用聯合：
SELECT PlayerCode, [Key], [Value]
FROM
(
SELECT PlayerCode, 'Name' AS [Key], Name AS [Value], 1 AS pos FROM yourTable
UNION ALL
SELECT PlayerCode, 'Age', CAST(Age AS varchar(3)), 2 FROM yourTable
UNION ALL
SELECT PlayerCode, 'Club', Club, 3 FROM yourTable
) t
ORDER BY PlayerCode, pos;
uj5u.com熱心網友回復：
兩個快速選項。
更高性能（并提供更多靈活性）
Select A.[PlayerCode]
,B.*
From YourTable A
Cross Apply ( values ('Name',[Name])
,('Age' ,concat('',[Age]))
,('Club',[Club])
) B([Key],[Value])
更動態（任意數量的列）
Select A.[PlayerCode]
,B.[Key]
,B.[Value]
From YourTable A
Cross Apply OpenJSON( (Select A.* For JSON Path,Without_Array_Wrapper ) ) B
Where [key] not in ('PlayerCode')
請注意，我們只需要排除[PlayerCode]并且不需要轉換資料型別。NULL 值也將被排除在外。
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/348035.html
標籤：sql sql-server 数据库 转轴
上一篇：Sql獲取即將到期的資料