Feeds:
Posts
Comments

Posts Tagged ‘XML PATH’

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:

RowToColumn_DataSet

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

RowToColumn_Option1

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:

  1. Main warp-around query (Parent)
  2. STUFF section
  3. 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

RowToColumn_Option2

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.

 

Hope this helps,
_Sqltimes

 

Advertisements

Read Full Post »