Case statement Help needed

Hello Farhan1,

Based on my understanding, to achieve your requirement you can follow the below sample.

USE TestDB
GO

CREATE TABLE ManageGroups(
P_ID Varchar(10) Not null DEFAULT('Abc13'),

Producttype varchar(20) not null DEFAULT('PCPC'),

LineofBusiness varchar(10) not null CHECK(LineofBusiness IN ('Comm' , 'Non comm'))
)
--drop table ManageGroups

INSERT INTO ManageGroups(P_ID,LineofBusiness)
VALUES
('Abc13','Comm'),
('bbc112','Non comm'),
('sam100','Comm'),
('sam100','Non comm')

SELECT DISTINCT P_ID ,
REPLACE(STUFF((SELECT ','+LineofBusiness
 FROM ManageGroups T1
 WHERE T1.P_ID=T2.P_ID
 FOR XML PATH('')),1,1,''),',','&') LineofBusiness FROM ManageGroups T2

DROP TABLE ManageGroups

/*
P_ID    LineofBusiness
Abc13   Comm
bbc112  Non comm
sam100  Comm&Non comm
*/

If you have any question, feel free to let me know.

---

Eric Zhang
TechNet Community Support