In this article, I shall show how to concatenate data from COLUMN1 by grouping against COLUMN2 without looping CURSOR.
Scenario
I want to concatenate data from
COLUMN1 by grouping against
COLUMN2 without looping CURSOR.
- Creating Sample Table
- Inserting Sample Data
- Fetching All Rows
- Concatenating data to Single Result (Row)
- Using COALESCE
- Using FOR XML PATH
- 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 TableIF 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.