question

ArunChandramouli-6978 avatar image
0 Votes"
ArunChandramouli-6978 asked ArunChandramouli-6978 commented

Query to concat values based on Unique key

Hi All,

Hope you are doing well!...I am trying to to group the values in the below input table by pickno..

DDL for Input table

create table input
(pickno varchar(50),
reasonorshipinstrction varchar(1000),
code varchar(2),
username varchar(45)
)
insert into input values
('L11230','side cracks','R','Tuli'),
('L11230','Funnel issue','R','Tuli'),
('L11230','Back to date 02/03/2021','S','Deemra'),
('L11230','Completed date changed 03/04/2021 to 09/04/2022','S','Alex'),
('M12401','Random repair ','R','Mina'),
('M12401','allergen','R','Alex'),
('M12401','returned from shop','S','Deemra'),
('M12401','this pickup is scheduled 04/2/2021','S','Tuli')

Basically I am trying to group by pickno and then group the code R into the column Reason and the code S into the column shipinstruction (combining the names and the reason or shipping instruction)..

create table output
(pickno varchar(20),
reason varchar(10000),
shipinstruction varchar(10000))

insert into output values
('L11230','name-Tuli,reason -side cracks ,name -Tuli, reason-Funnel issue','name-Deemra,shippinginstruction-Back to date 02/03/2021,name-Alex,shippinginstruction -Completed date changed 03/04/2021 to 09/04/2022'),
('M12401','name-Mina,reason-random repair,name-Alex, reason-allergen','name-Deemra,shippinginstruction- returnedfromshop,name-Tuli ,shippinginstruction -this pickup is scheduled 04/2/2021')




sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

NaomiNNN avatar image
0 Votes"
NaomiNNN answered ArunChandramouli-6978 commented

Try (for SQL 2017 and up):

 declare @input TABLE
 (pickno varchar(50),
 reasonorshipinstrction varchar(1000),
 code varchar(2),
 username varchar(45)
 )
 insert into @input values
 ('L11230','side cracks','R','Tuli'),
 ('L11230','Funnel issue','R','Tuli'),
 ('L11230','Back to date 02/03/2021','S','Deemra'),
 ('L11230','Completed date changed 03/04/2021 to 09/04/2022','S','Alex'),
 ('M12401','Random repair ','R','Mina'),
 ('M12401','allergen','R','Alex'),
 ('M12401','returned from shop','S','Deemra'),
 ('M12401','this pickup is scheduled 04/2/2021','S','Tuli')
    
 SELECT pickno, STRING_AGG(CASE WHEN [code] = 'R' THEN 'name-'+ username + ',reason-' + RTRIM(reasonorshipinstrction) END, ', ') AS [reason],
 STRING_AGG(CASE WHEN [code] = 'S' THEN 'name-'+ username + ',shipping instruction-' + RTRIM(reasonorshipinstrction) END, ', ') AS [shipping instruction]
 FROM @input
 GROUP BY [@input].pickno
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @NaomiNNN ..This works !..Really appreciate your help!..

0 Votes 0 ·