我希望根據類似的特定列值合并所有列。
假設我在“ DeckName ”中有類似的值，因此使用Column2我希望將所有不同的其他列值合并為一個。
輸入表：
DeckId DeckName Location Items Places UserName
XK001 NOVA USA Cream ASIA-P NOVA001
XK002 NOVA IND DEO AFRICA NOVA002
XK001 NOVA USA Cosmetic ASIA-P NOVA003
VK001 VEET RUS PROFIX UK VEET001
VK003 VEET CHI Cream ASIA-P VEET002
VK002 VEET NED WAX RUSSIA VEET001
NS001 PHLIPS USA Cream ASIA-P PHLPS001
PS001 RUDS USA Cream ASIA-P VLT001
預期輸出表值
DeckId DeckName Location Items Places UserName
XK001; XK002 NOVA USA; IND Cream; DEO; Cosmetic ASIA-P; AFRICA NOVA001; NOVA002; NOVA003
VK001; VK003; VK002 VEET RUS; CHI; NED PROFIX; Cream; WAX UK; ASIA-P; RUSSIA VEET001; VEET002
NS001 PHLIPS USA Cream ASIA-P PHLPS001
PS001 RUDS USA Cream ASIA-P VLT001
請提出一個查詢以找到繼續預期的結果：
uj5u.com熱心網友回復：
你在嘗試類似的東西：
select STRING_AGG(DeckId,';') as DeckId ,
DeckName ,
STRING_AGG(Location,';') as Location,
STRING_AGG(Items,';') as Items,
STRING_AGG(Places,';') as Places,
STRING_AGG(UserName,';') as UserName
from test_tbl
group by DeckName;
演示：https : //dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=ece626d2ce2919c2148cccbe83e4634e
檢查更多資訊：https : //docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15
對于 Older SQL Server Version ，可能不是最好的解決方案，但您可以嘗試：
SELECT DISTINCT ST2.DeckName,
SUBSTRING(
(
SELECT ';' DeckId AS [text()]
FROM test_tbl ST1
WHERE ST1.DeckName = ST2.DeckName
FOR XML PATH (''), TYPE
).value('text()[1]','nvarchar(max)'), 2, 9999) [DeckId],
SUBSTRING(
(
SELECT ';' Location AS [text()]
FROM test_tbl ST1
WHERE ST1.DeckName = ST2.DeckName
FOR XML PATH (''), TYPE
).value('text()[1]','nvarchar(max)'), 2, 9999) [Location] ,
SUBSTRING(
(
SELECT ';' Items AS [text()]
FROM test_tbl ST1
WHERE ST1.DeckName = ST2.DeckName
FOR XML PATH (''), TYPE
).value('text()[1]','nvarchar(max)'), 2, 9999) [Items] ,
SUBSTRING(
(
SELECT ';' Places AS [text()]
FROM test_tbl ST1
WHERE ST1.DeckName = ST2.DeckName
FOR XML PATH (''), TYPE
).value('text()[1]','nvarchar(max)'), 2, 9999) [Places] ,
SUBSTRING(
(
SELECT ';' UserName AS [text()]
FROM test_tbl ST1
WHERE ST1.DeckName = ST2.DeckName
FOR XML PATH (''), TYPE
).value('text()[1]','nvarchar(max)'), 2, 9999) [UserName]
FROM test_tbl ST2
演示：https : //dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=53aef222b43a50f6e4874e3b307a018b
uj5u.com熱心網友回復：
您使用的是哪個版本的 SQL 服務器？
如果您使用的是 2017 及以上版本，則可以在 sql server 中使用 string_agg 函式（與 Oracle 中的 listagg 相同）。像下面這樣 -
select deck_name,
STRING_AGG(deck_id, ';') within group(order by deck_name) as deck_id,
STRING_AGG(Location, ';') within group(order by deck_name) as location,
STRING_AGG(items, ';') within group(order by deck_name) as items,
STRING_AGG(places, ';') within group(order by deck_name) as place,
STRING_AGG(username, ';') within group(order by deck_name) as username
from deck_table dt
group by deck_name
對于舊版本，您必須使用帶有 XML 路徑的 STUFF 函式。我無法運行它，您可能必須使用 XML 運算式，但您可以嘗試以下操作 -
select deck_name,
STUFF((
SELECT distinct ';' dt1.deck_id
FROM deck_table dt1
WHERE dt1.deck_name = dt.deck_name
ORDER BY dt1.deck_id
FOR XML PATH('')), 1, LEN(','), '') AS deck_id,
STUFF((
SELECT distinct ';' dt1.location
FROM deck_table dt1
WHERE dt1.deck_name = dt.deck_name
ORDER BY dt1.location
FOR XML PATH('')), 1, LEN(','), '') AS location,
STUFF((
SELECT distinct ';' dt1.items
FROM deck_table dt1
WHERE dt1.deck_name = dt.deck_name
ORDER BY dt1.items
FOR XML PATH('')), 1, LEN(','), '') AS items,
STUFF((
SELECT distinct ';' dt1.place
FROM deck_table dt1
WHERE dt1.deck_name = dt.deck_name
ORDER BY dt1.place
FOR XML PATH('')), 1, LEN(','), '') AS place,
STUFF((
SELECT distinct ';' dt1.username
FROM deck_table dt1
WHERE dt1.deck_name = dt.deck_name
ORDER BY dt1.username
FOR XML PATH('')), 1, LEN(','), '') AS username
from deck_table dt
group by deck_name
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/391566.html
標籤：sql-server
下一篇：具有固定符號數的MSSQL列