從一行創建2個逗號分隔的列

大家好我正在嘗試創建一個查詢來創建兩個單獨的列，其中一行中的逗號分隔值。

下面是創建表陳述句

CREATE TABLE [logs-table](
[AccountId] [uniqueidentifier] NULL,
[UtcActionDate] [datetime] NULL,
[ActionType] [nvarchar](255) NULL,
[ActionSubType] [nvarchar](255) NULL,
[PrimaryArgs] [nvarchar](255) NULL
)

CREATE TABLE [dbo].[Accounts](
[Id] [uniqueidentifier] NOT NULL,
[EmailAddress] [nvarchar](255) NULL,
)

INSERT INTO logs_table(AccountId,UtcActionDate,ActionType,ActionSubType,PrimaryArgs)
VALUES ('7DADAEDB-A0E5-4290-8D94-8D3C8144A662','2022-02-15 16:12:56.000', 'Exchange','AddedToWatchlist', 'Trade-share1'),
('7DADAEDB-A0E5-4290-8D94-8D3C8144A662','2022-02-15 16:55:22.000', 'Exchange','AddedToWatchlist','Trade-share2'),
('7DADAEDB-A0E5-4290-8D94-8D3C8144A662','2022-02-15 16:55:27.000', 'Exchange','AddedToWatchlist','Issuer-SPV'),
('7DADAEDB-A0E5-4290-8D94-8D3C8144A662','2022-02-15 16:55:27.000', 'Exchange','AddedToWatchlist','Issuer-SPV'),

('9DADAEDB-A0E5-4290-8D94-8D3C8144A662','2022-02-15 16:55:22.000', 'Exchange','AddedToWatchlist','Trade-share1'),
('9DADAEDB-A0E5-4290-8D94-8D3C8144A662','2022-02-15 16:55:27.000', 'Exchange','AddedToWatchlist','Issuer-SPV'),
('9DADAEDB-A0E5-4290-8D94-8D3C8144A662','2022-02-15 16:55:22.000', 'Exchange','AddedToWatchlist','Trade-share2'),
('9DADAEDB-A0E5-4290-8D94-8D3C8144A662','2022-02-15 16:55:27.000', 'Exchange','AddedToWatchlist','Issuer-SPV2'),
('9DADAEDB-A0E5-4290-8D94-8D3C8144A662','2022-02-15 16:55:27.000', 'Exchange','AddedToWatchlist','Issuer-SPV2'),



INSERT INTO Accounts
VALUES ('7DADAEDB-A0E5-4290-8D94-8D3C8144A662','[email protected]'),
('9DADAEDB-A0E5-4290-8D94-8D3C8144A662','[email protected]'),
The output I want will look like this


Account ID                           EmailAddress       Trade         Issuer
7DADAEDB-A0E5-4290-8D94-8D3C8144A662 tesuser1@gmail.com share1,share2 SPV
9DADAEDB-A0E5-4290-8D94-8D3C8144A662 tesuser2@gmail.com share1,share2 SPV,SPV2

我有這個查詢

SELECT Id,EmailAddress,STRING_AGG(Trade,',') AS Trade,STRING_AGG(Issuers,',') AS Issuer
 FROM (SELECT A.Id,A.EmailAddress,
              CASE WHEN L.PrimaryArgs LIKE'Trade%' THEN TRIM(RIGHT(L.PrimaryArgs, LEN(L.PrimaryArgs) - CHARINDEX('-', L.PrimaryArgs))) END AS Trade,
              CASE WHEN L.PrimaryArgs LIKE'Issuer%' THEN TRIM(RIGHT(L.PrimaryArgs, LEN(L.PrimaryArgs) - CHARINDEX('-', L.PrimaryArgs))) END AS Issuers 
       FROM Accounts A JOIN logs_table L ON A.Id=L.AccountId 
      )T
 GROUP BY Id,EmailAddress

我得到的輸出是

    Account ID                           EmailAddress       Trade         Issuer
    7DADAEDB-A0E5-4290-8D94-8D3C8144A662 tesuser1@gmail.com share1,share2 SPV,SPV
    9DADAEDB-A0E5-4290-8D94-8D3C8144A662 tesuser2@gmail.com share1,share2 SPV,SPV2,SPV2

但是對于第一個用戶，SPV 將出現兩次，第二個 SPV2 將出現兩次。如何避免和僅在逗號分隔串列中添加不同的值？

uj5u.com熱心網友回復：

您只需將distinct關鍵字添加到派生表中：

SELECT Id,EmailAddress,STRING_AGG(Trade,',') AS Trade,STRING_AGG(Issuers,',') AS Issuer
 FROM (select DISTINCT A.Id,A.EmailAddress,
              CASE WHEN L.PrimaryArgs LIKE'Trade%' THEN TRIM(RIGHT(L.PrimaryArgs, LEN(L.PrimaryArgs) - CHARINDEX('-', L.PrimaryArgs))) END AS Trade,
              CASE WHEN L.PrimaryArgs LIKE'Issuer%' THEN TRIM(RIGHT(L.PrimaryArgs, LEN(L.PrimaryArgs) - CHARINDEX('-', L.PrimaryArgs))) END AS Issuers 
       FROM Accounts A JOIN logs_table L ON A.Id=L.AccountId 
      )T
 GROUP BY Id,EmailAddress

uj5u.com熱心網友回復：

將 GROUP BY 添加到您的子查詢：

SELECT Id,EmailAddress,STRING_AGG(Trade,',') AS Trade,STRING_AGG(Issuers,',') AS Issuer
 FROM (SELECT A.Id,A.EmailAddress,
              CASE WHEN L.PrimaryArgs LIKE'Trade%' THEN TRIM(RIGHT(L.PrimaryArgs, LEN(L.PrimaryArgs) - CHARINDEX('-', L.PrimaryArgs))) END AS Trade,
              CASE WHEN L.PrimaryArgs LIKE'Issuer%' THEN TRIM(RIGHT(L.PrimaryArgs, LEN(L.PrimaryArgs) - CHARINDEX('-', L.PrimaryArgs))) END AS Issuers 
       FROM Accounts A JOIN logs_table L ON A.Id=L.AccountId 
       GROUP BY a.id, 
                a.emailaddress,
                CASE WHEN L.PrimaryArgs LIKE'Trade%' THEN TRIM(RIGHT(L.PrimaryArgs, LEN(L.PrimaryArgs) - CHARINDEX('-', L.PrimaryArgs))) END,
                CASE WHEN L.PrimaryArgs LIKE'Issuer%' THEN TRIM(RIGHT(L.PrimaryArgs, LEN(L.PrimaryArgs) - CHARINDEX('-', L.PrimaryArgs))) END
      )T
 GROUP BY Id,EmailAddress

dbfiddle.uk

標籤：sql sql服务器

上一篇：如何將3列交叉連接在一起？

下一篇：如何在postgress中的WhereIn子句上使用Concat