Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Aggregrating varchar columns

By Vikram Takrani,

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.

Total article views: 1507 | Views in the last 30 days: 2
 
Related Articles
FORUM

help insert employee to #temp table all month

and loop insert until the last employee

FORUM

Tricky ...VARCHAR

VARCHAR logics

ARTICLE

Selecting from hierarchies like Managers and Employees

Chuck Hoffman shows a technique using sets for selecting records from hierarchies such as Manager / ...

FORUM

I would like to insert from the clipboard text with line breaks in a "varchar" column.

I would like to insert from the clipboard text with line breaks in a "varchar" column.

FORUM

sql varchar(max) to Mysql (Longtext)

can't insert data from sql varchar(max) into Mysql(longtext)

Tags
 
Contribute