Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: 996 | Views in the last 30 days: 1
 
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

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones