Create 2 comma separated column from one row-CodePudding

Hi Everyone I am trying to create a query to create two separate columns with comma separated values from one row.

Below is the create table statement

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 [email protected] share1,share2 SPV
9DADAEDB-A0E5-4290-8D94-8D3C8144A662 [email protected] share1,share2 SPV,SPV2

I have this query

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

The output I am getting is

    Account ID                           EmailAddress       Trade         Issuer
    7DADAEDB-A0E5-4290-8D94-8D3C8144A662 [email protected] share1,share2 SPV,SPV
    9DADAEDB-A0E5-4290-8D94-8D3C8144A662 [email protected] share1,share2 SPV,SPV2,SPV2

but for 1st user SPV will come twice will for second SPV2 will come twice. How to avoid and add only distinct values in comma separated list?

CodePudding user response：

You simply need to add distinct keyword to your derived table:

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

CodePudding user response：

Add a GROUP BY to your sub-query:

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