Assuming you have a table Employee with below schema
CREATE TABLE [dbo].[Employee](
[id] [int] NOT NULL,
[status] [varchar](20) NOT NULL,
[add1] [varchar](20) NOT NULL,
[add2] [varchar](20) NULL
)
--Populate Test Data
INSERT INTO [Employee] VALUES (1,'S1','S1Add1','S1Add2')
INSERT INTO [Employee] VALUES (1,'S2','S2Add1',NULL)
INSERT INTO [Employee] VALUES (1,'S3','S3Add1',NULL)
INSERT INTO [Employee] VALUES (1,'S4','S4Add1','S4Add2')
INSERT INTO [Employee] VALUES (2,'S1','S1Add1','S1Add2')
INSERT INTO [Employee] VALUES (2,'S2','S2Add1',NULL)
INSERT INTO [Employee] VALUES (2,'S3','S3Add1','S3Add2')
Requirement :
Columns Status,Add1 and Add2 needs to be grouped for all the rows with the same index as a comma delimited string.
e.g. in the above data we need the output as:
Id CSVs
1 S1,S1Add1,S1Add2,S2,S2Add1,,S3,S3Add1,,S4,S4Add1,S4Add2
2 S1,S1Add1,S1Add2,S2,S2Add1,,S3,S3Add1,S3Add2
Above SQL uses below approach:
- Get row numbers for a particular index
- Recursively loop thru and append the column values till last row number
Alternative approaches could be writing a stored proc/function to do this logic of concatenation.
