'Create 2 comma separated column from one row

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?

sqlsql-server


Solution 1:[1]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 FlexYourData

Related Questions