从一行创建2个逗号分隔列

Question

大家好，我正在尝试创建一个查询，以创建两个带有逗号分隔值的列。

下面是create语句

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','tesuser1@gmail.com'),
('9DADAEDB-A0E5-4290-8D94-8D3C8144A662','tesuser2@gmail.com'),
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将出现两次。如何避免并仅在逗号分隔列表中添加不同的值？

Answer 1

只需将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

Answer 2

在子查询中添加一个组：

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