Output sub query as CSV when Joining tables (one to many relationship)

This will show you how to get something like this from a table join and then I’ll explain how the SQL works.

Name csv
UK London,Manchester,Birmingham
USA New Your,Washington,Kentucky
France Paris,Leon,Frejus

Suppose we have two tables

-- Create base table
CREATE TABLE [dbo].[TableA](
[id] [int] NOT NULL,
[Name] [varchar](50) NULL
)

-- Create secondary table with foreign ley
CREATE TABLE [dbo].[TableB](
[fk_tableA] [int] NOT NULL,
[Detail] [varchar](50) NULL,
)

Lets put countries in the first one and cities in the second

-- Create test data (countries)
INSERT INTO TableA (id,Name) VALUES (1,'UK')
INSERT INTO TableA (id,Name) VALUES (2,'USA')
INSERT INTO TableA (id,Name) VALUES (3,'France')

-- Create secondary test data (cities)
INSERT INTO TableB (fk_tableA,Detail) VALUES (1,'London'),(1,'Manchester'),(1,'Birmingham')
INSERT INTO TableB (fk_tableA,Detail) VALUES (2,'New Your'),(2,'Washington'),(2,'Kentucky')
INSERT INTO TableB (fk_tableA,Detail) VALUES (3,'Paris'),(3,'Leon'),(3,'Frejus')

Now lets read the data from these tables in the usual way. That is: joined using INNER JOIN and output as a big multi row list.

-- Show the data linked in the data in usual way
SELECT A.Name, B.Detail
FROM TableA A
INNER JOIN TableB B ON B.fk_tableA = A.id
ORDER BY A.Name, B.Detail

Name Detail
France Frejus
France Leon
France Paris
UK Birmingham
UK London
UK Manchester
USA Kentucky
USA New Your
USA Washington

What if we want to see this instead

Name csv
UK London,Manchester,Birmingham
USA New Your,Washington,Kentucky
France Paris,Leon,Frejus

We can join the tables using this weird query with XML

SELECT
A.Name
,STUFF(SELECT ',' + CAST(B.detail AS VARCHAR(MAX))
FROM TableB B WHERE B.fk_tableA = A.id
FOR XML PATH('')),1,1,'') AS csv
FROM TableA A
GROUP BY
A.id, A.Name

Why does this work?

Well obviously the outer query just gets the list of country names and groups them by name so ignore that

Looking at just the inner query with the STUFF and XML removed

SELECT ',' + CAST(B.detail AS VARCHAR(MAX)) FROM TableB B WHERE B.fk_tableA = X

For each value of X this simply gets “, value of detail” so where X=id if UK we get…

,London
,Manchester
,Birmingham

FOR XML PATH(''))

simply wraps this in to a single line of text. The ” bit prevents the surrounding XML data node

“,London,Manchester,Birmingham”

Actually it goes into an automatically named xml column that SQL can easily convert to a string if it needs to

for xml path

Note that the text has a leading comma which we do not want. We are going to get rid of this with the stuff but before we do that we need the xml converted to varchar as stuff is a string function.

CAST(B.detail AS VARCHAR(MAX))

Now we have string “,London,Manchester,Birmingham” so get rid of the leading comma

Some of you may ask “Why did we not use SubString?”
Substring requires a length argument and in this case we do not know what this is. We could do it this way but we may get unpredictable results including string truncation. e.g. the following will work fine.
SUBSTRING( ',London,Manchester,Birmingham' ,2,100)

but this would be disastrous
SUBSTRING( ',London,Manchester,Birmingham' ,2,10)

By using Stuff we are simply inserting nothing in to position 1 and replacing 1 character which is the comma

i.e. ‘,London,Manchester,Birmingham’ becomes ‘London,Manchester,Birmingham’

So now the outer query handles the grouping in to the master data presented by the base table and the subquery substitutes the value of the outer column in to the inner query.

Its quite simple really but I think the combination of statements as a whole is a little daunting, particularly the XMl bit that throws people in to believing they are using XML in some mysterious way which although you are, you are really just using a feature of the conversion to XML (smashing data in to a single unit) and no actual XML is involved.

Here’s the code as a single block that you can copy and paste in to SQL Management Studio. Just create a new test database, open a new query, cut, paste and run this entire script to make the tables and show the output.

Complete example code

———————————————————

-- Create base table
CREATE TABLE [dbo].[TableA](
[id] [int] NOT NULL,
[Name] [varchar](50) NULL
)
-- Create secondary table with foreign ley
CREATE TABLE [dbo].[TableB](
[fk_tableA] [int] NOT NULL,
[Detail] [varchar](50) NULL,
)

— Create test data (countries)
INSERT INTO TableA (id,Name) VALUES (1,’UK’)
INSERT INTO TableA (id,Name) VALUES (2,’USA’)
INSERT INTO TableA (id,Name) VALUES (3,’France’)

— Create secondary test data (cities)
INSERT INTO TableB (fk_tableA,Detail) VALUES (1,’London’),(1,’Manchester’),(1,’Birmingham’)
INSERT INTO TableB (fk_tableA,Detail) VALUES (2,’New Your’),(2,’Washington’),(2,’Kentucky’)
INSERT INTO TableB (fk_tableA,Detail) VALUES (3,’Paris’),(3,’Leon’),(3,’Frejus’)

— Show data
SELECT * FROM TableA
SELECT * FROM TableB

— Show the data linked in the data in usual way
SELECT A.Name, B.Detail
FROM TableA A
INNER JOIN TableB B ON B.fk_tableA = A.id
ORDER BY A.Name, B.Detail

SELECT A.Name
,STUFF((
SELECT ‘,’ + CAST(B.detail AS VARCHAR(MAX))
FROM TableB B WHERE B.fk_tableA = A.id
FOR XML PATH(”)),1,1,”) AS csv
FROM TableA A
GROUP BY A.id, A.Name

———————————————————

Advertisements

About davidbridge

I am a contractor for David Bridge Technology Limited specialising in database design and Web development using Microsoft technologies such as c#, MVC .net and SQL Server (full stack)
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s