Create 2 comma separated column from one row

Question

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

Below is the create table statement

CREATE TABLE logs-table

CREATE TABLE [dbo].Accounts

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'),

('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'),

INSERT INTO Accounts
VALUES ('7DADAEDB-A0E5-4290-8D94-8D3C8144A662','tesuser1@Stuff .com'),
('9DADAEDB-A0E5-4290-8D94-8D3C8144A662','tesuser2@Stuff .com'),
The output I want will look like this

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

I have tried to create query using self join but it's returning duplicate records

select A.Id, A.EmailAddress,
STRING_AGG (TRIM(RIGHT(TSPV.PrimaryArgs, LEN(TSPV.PrimaryArgs) - CHARINDEX('-', TSPV.PrimaryArgs))),',') AS 'Trade' ,
STRING_AGG (TRIM(RIGHT(TBT.PrimaryArgs, LEN(TBT.PrimaryArgs) - CHARINDEX('-', TBT.PrimaryArgs))),',') AS 'Issuers'
FROM Accounts A
INNER JOIN logs_table TSPV ON A.Id= TSPV.AccountId AND TSPV.PrimaryArgs LIKE '%Trade%' AND TSPV.ActionSubType IN ('AddedToWatchlist') AND TSPV.ActionType='Exchange'
INNER JOIN logs_table TBT ON A.Id= TBT.AccountId AND TBT.PrimaryArgs LIKE '%Issuer%' AND TBT.ActionSubType IN ('AddedToWatchlist') AND TBT.ActionType='Exchange'
Group by A.Id,A.emailaddress

Any idea how can I acheive the required output? I am doing this in view so no temp tables or curosrs?

Answer 1

Hi @jahanzaib rahman
Check 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  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

@LiHong-MSFT . Thanks . That works great. One question what if I have duplicate values in data and I want to show only once. For example

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'),

Now 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?