Quick one today:
Quite often, we all run into this scenario where we need to convert column data into row with either comma or some other delimiter.
Below, we’ll see a couple of ways; Each serving a different purpose:
Build data set first:
--
-- Convert row data into column (without using PIVOT)
--
-- Create tables
CREATE TABLE Parent (
ID INT IDENTITY(1,1)
, Name VARCHAR(20)
)
CREATE TABLE Child (
ID INT IDENTITY(1,1)
, ParentID INT
, Name VARCHAR(25)
)
-- Create Parent records
INSERT INTO Parent (Name) VALUES ('Mary')
INSERT INTO Parent (Name) VALUES ('Patel')
INSERT INTO Parent (Name) VALUES ('Risvic')
-- Create child records
INSERT INTO Child (ParentID, Name) values (1, 'Jil')
INSERT INTO Child (ParentID, Name) values (1, 'Jim')
INSERT INTO Child (ParentID, Name) values (1, 'Kat')
INSERT INTO Child (ParentID, Name) values (2, 'Raja')
INSERT INTO Child (ParentID, Name) values (2, 'Kamat')
GO
SELECT * FROM Parent
SELECT * FROM Child
GO
Data set looks like this:
Option 1 : Using Variable
This method gives us result in comma delimited fashion, but it only works for one parent at a time; And we cannot combine Parent name as a column next to Children column, as Sql Server does not allow combining data-retrieval with variable manipulation query.
It spits out an error like this:
Msg 141, Level 15, State 1, Line 35 A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
-- -- Option 1 : using @Variable -- DECLARE @Children VARCHAR(8000) SELECT @Children = COALESCE(@Children, '') + ', ' + Name FROM dbo.Child WHERE ParentID = 1 SELECT STUFF(@Children, 1, 2, '') GO
Option 2 : Using XML
XML PATH gives us more control over retrieval manipulation. It allows us to retrieve data in a tabular fashion, while keeping the children names into one column.
Breakdown:
This below query has 3 parts:
- Main warp-around query (Parent)
- STUFF section
- XML PATH section
--
-- Using XML
--
SELECT P.ID AS [ParentID]
, P.Name AS [ParentName]
, STUFF(
(
SELECT ', ' + C.Name
FROM dbo.Child AS C
WHERE C.ParentID = P.ID
FOR XML PATH('')
)
, 1, 2, ''
) AS [Children]
FROM dbo.Parent AS P
GO
--
-- Clean up
--
DROP TABLE Parent
DROP TABLE Child
GO
We’ll see the details of each query in reverse order:
XML PATH Query
This is the most important part of this whole query. It allows us to retrieve all children of a given parent in a comma delimited fashion.
Without FOR XML PATH(”) statement, this is just a query that returns children as rows. When you add FOR XML, that data set is now converted into XML format (sort of).
PATH (”) makes sure that the the element trees notation is replaced with whatever is in between ” (empty). So we get a simple comma delimited child list.
Now we have an extra comma to remove. That’s where STUFF comes into play.
STUFF query section replaces comma with empty space.
Main wrap-around query is to bring this all together with ParentID & Parent Name to make it more usable.
_Sqltimes
Playing with database servers... 