Search
Winners

Win Prizes

Social Presence
Twitter Twitter LinkedIn YouTube Google

Like us on Facebook
Advertisements
Top Articles Author
Sat, 01-Nov-2014 Authors
All Time Authors
Sourav.Kayal
39750
Sheonarayan
38050
Niladri.Biswas
33350

Latest members | More ...


(Statistics delayed by 5 minutes)
Ads
 Article

Data Concatenation - SQL Server

Pandians
Posted by under Sql Server category on for Intermediate level | Views : 9634 red flag
If you found plagiarised (copied) or inappropriate content,
please let us know the original source along with your correct email id (to communicate) for further action.
Rating: 5 out of 5
1 vote(s)
In this article, I shall show how to concatenate data from COLUMN1 by grouping against COLUMN2 without looping CURSOR.

 Download source code for Data Concatenation - SQL Server


Scenario 
I want to concatenate data from COLUMN1 by grouping against COLUMN2 without looping CURSOR.

  1. Creating Sample Table
  2. Inserting Sample Data
  3. Fetching All Rows
  4. Concatenating data to Single Result (Row)
      1. Using COALESCE 
      2. Using FOR XML PATH 
  5. Concatenating data with Multiple Result(s)


Introduction
We can Concatenate data in various way(s) in various scenarios using COALESCE, FOR XML PATH, LOOP with CURSOR(s).Etc.,

But. we are going to do with COALESCE and FOR XML PATH(Not loop with CURSOR

1. Creation Sample Table

IF OBJECT_ID('DataTypeCategory','U') IS NULL
CREATE
TABLE DataTypeCategory
(

ID              INT IDENTITY(1,1)    ,
Category     VARCHAR(50)           ,
DataType    VARCHAR(20)
)

GO

2. Inserting Sample Data


INSERT DataTypeCategory VALUES('Exact Numerics','Bigint')
INSERT
DataTypeCategory VALUES('Exact Numerics','Decimal')
INSERT
DataTypeCategory VALUES('Exact Numerics','Int')
INSERT
DataTypeCategory VALUES('Exact Numerics','Numeric')
INSERT
DataTypeCategory VALUES('Exact Numerics','Smallint')
INSERT
DataTypeCategory VALUES('Exact Numerics','Money')
INSERT
DataTypeCategory VALUES('Exact Numerics','Tinyint')
INSERT
DataTypeCategory VALUES('Exact Numerics','Smallmoney')
INSERT
DataTypeCategory VALUES('Exact Numerics','Bit')
INSERT
DataTypeCategory VALUES('Approximate Numerics','Float')
INSERT
DataTypeCategory VALUES('Approximate Numerics','Real')
INSERT
DataTypeCategory VALUES('Date and Time','Datetime')
INSERT
DataTypeCategory VALUES('Date and Time','Smalldatetime')
INSERT
DataTypeCategory VALUES('Character Strings','Char')
INSERT
DataTypeCategory VALUES('Character Strings','Text')
INSERT
DataTypeCategory VALUES('Character Strings','Varchar')
INSERT
DataTypeCategory VALUES('Unicode Character Strings','Nchar')
INSERT
DataTypeCategory VALUES('Unicode Character Strings','Ntext')
INSERT
DataTypeCategory VALUES('Unicode Character Strings','Nvarchar')
INSERT
DataTypeCategory VALUES('Binary Strings','Binary')
INSERT
DataTypeCategory VALUES('Binary Strings','Image')
INSERT
DataTypeCategory VALUES('Binary Strings','Varbinary')
INSERT
DataTypeCategory VALUES('Other Data Types','Cursor')
INSERT
DataTypeCategory VALUES('Other Data Types','Timestamp')
INSERT
DataTypeCategory VALUES('Other Data Types','Sql_variant')
INSERT
DataTypeCategory VALUES('Other Data Types','Uniqueidentifier')
INSERT
DataTypeCategory VALUES('Other Data Types','Table')
INSERT
DataTypeCategory VALUES('Other Data Types','Xml')
GO

3. Fetching All Rows

Fetching all row(s) from the table 
SELECT * FROM DataTypeCategory
GO




4. Concatenating data with Single Result
If we want to concatenate all DataType data only for Category='Exact Numerics' only


4.1. Using COALESCE

DECLARE @StrConcate_COALESCE VARCHAR(MAX)
SELECT @StrConcate_COALESCE = COALESCE(@StrConcate_COALESCE,'') + DataType + ',' FROM DataTypeCategory WHERE Category='Exact Numerics'
SELECT LEFT(@StrConcate_COALESCE,LEN(@StrConcate_COALESCE)-1) 'COALESCE'
GO



4.2. Using FOR XML PATH 

DECLARE @StrConcate_FORXMLPATH VARCHAR(MAX)
SELECT @StrConcate_FORXMLPATH = (SELECT DataType + ',' FROM DataTypeCategory WHERE Category='Exact Numerics' FOR XML PATH(''))
SELECT LEFT(@StrConcate_FORXMLPATH,LEN(@StrConcate_FORXMLPATH)-1) 'FOR XML PATH'
GO


5. Concatenating data with Multiple Result(s)
If we want to concatenate all DataType data for each Category wise.


;
WITH Concatenation(Category,Concatenated) AS
(
SELECT A.Category,
(
SELECT B.DataType + ',' FROM DataTypeCategory B WHERE B.Category=A.Category FOR XML PATH('')) 'Concatinated'
FROM
DataTypeCategory A
GROUP
BY A.Category
)

SELECT
Category,LEFT(Concatenated,LEN(Concatenated)-1) 'Concatenated' FROM Concatenation
GO




Conclusion

Each group wise concatenation can be done by Looping with CURSOR also. But doing FOR XML PATH is less code and we can avoid of CURSORs.

Page copy protected against web site content infringement by Copyscape
About the Author

Pandians

Full Name: Pandian Sathappan
Member Level: Silver
Member Status: Member,MVP
Member Since: 5/11/2009 2:24:28 AM
Country: India
Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions
http://sqlserverbuddy.blogspot.com/

Microsoft Certification - MCITP: Microsoft Certified IT Professional - SQL Server 2008 Database Administrator - MCTS: Microsoft Certified Technology Specialist - SQL Server 2005 - MCP: Microsoft Certified Professional - SQL Server 2000
Login to vote for this post.
Found interesting? Add this to:


Comments or Responses

Posted by: Raja on: 7/21/2009

Very efficient code, Thanks Sathappan.

Login to post response

Comment using Facebook(Author doesn't get notification)