SQL Server table result to array in SQL Server 2005

Question

Hi and thanks in advance.

First off, I do not mean I want to convert an array into a table. I can do that. Easy :)

I would like to do the opposite.

I have a row with n rows related to it in another table. I wish to output the row and then within a single column of that row, let's say Children, its linked rows in an array (or a string that I can interpret as an array with front-end code).

Like this:

ID TITLE DESCRIPTION CHILDREN
--------------------------------------------------------
36 Blah Blah Blah ['Bob','Gary','Reginald']
20 Pah Pah Pah ['Emily','Dave']

You see?

I know, this is probably not the best way to do it. But, it's for a specific use that would be too long to explain here.

Thanks again.

• Seph: possible duplicate of stackoverflow.com/questions/6899/…
• jamesmhaley: Yea it is, sorry. My problem is I was not searching for the word concatenate!

Answer 1

You can actually do this all in one CTE select query, without using any functions. Here's how:

First, consider this parent/child table structure:

CREATE TABLE P (ID INT PRIMARY KEY, Description VARCHAR(20));
CREATE TABLE C (ID INT PRIMARY KEY, PID INT, 
 Description VARCHAR(20), 
 CONSTRAINT fk FOREIGN KEY (PID) REFERENCES P(ID));

(I've use P and C to save on typing!)

And lets add some test data, matching that of the question asker:

INSERT INTO P VALUES (36, 'Blah Blah');
INSERT INTO P VALUES (20, 'Pah Pah');
INSERT INTO C VALUES (1, 36, 'Bob');
INSERT INTO C VALUES (2, 36, 'Gary');
INSERT INTO C VALUES (3, 36, 'Reginald');
INSERT INTO C VALUES (4, 20, 'Emily');
INSERT INTO C VALUES (5, 20, 'Dave');

Then finally, the CTE expression:

WITH
FirstItems (PID, FirstCID) AS ( 
 SELECT C.PID, MIN(C.ID)
 FROM C
 GROUP BY C.PID 
), 
SubItemList (PID, CID, ItemNum) AS (
 SELECT C.PID, C.ID, 1
 FROM C JOIN FirstItems FI ON (C.ID = FI.FirstCID)
 UNION ALL
 SELECT C.PID, C.ID, IL.ItemNum + 1
 FROM C JOIN SubItemList IL ON C.PID = IL.PID AND C.ID > CID
),
ItemList (PID, CID, ItemNum) AS (
 SELECT PID, CID, MAX(ItemNum)
 FROM SubItemList
 GROUP BY PID, CID
),
SubArrayList (PID, CID, Array, ItemNum) AS (
 SELECT IL.PID, IL.CID, CAST(C.Description AS VARCHAR(MAX)), IL.ItemNum
 FROM ItemList IL JOIN C ON IL.CID = C.ID
 WHERE IL.ItemNum = 1
 UNION ALL
 SELECT IL.PID, IL.CID, AL.Array + ',' + CAST(C.Description AS VARCHAR(MAX)), IL.ItemNum
 FROM ItemList IL
 JOIN SubArrayList AL ON (IL.PID = AL.PID AND IL.ItemNum = AL.ItemNum + 1)
 JOIN C ON (IL.CID = C.ID)
),
MaxItems (PID, MaxItem) AS (
 SELECT PID, MAX(ItemNum)
 FROM SubItemList
 GROUP BY PID
),
ArrayList (PID, List) AS (
 SELECT SAL.PID, SAL.Array
 FROM SubArrayList SAL 
 JOIN MaxItems MI ON (SAL.PID = MI.PID AND SAL.ItemNum = MI.MaxItem)
)
SELECT P.ID, P.Description, AL.List
 FROM ArrayList AL JOIN P ON P.ID = AL.PID
 ORDER BY P.ID

Result:

ID Description List
-- -------------- --------
20 Pah Pah Emily,Dave
36 Blah Blah Bob,Gary,Reginald 

To explain what's going on here, I'll describe each part of the CTE and what it does.

FirstItems looks at all the children, and finds the lowest ID in each parent group to use as the anchor for the next recursive SELECT:

FirstItems (PID, FirstCID) AS (
 SELECT C.PID, MIN(C.ID)
 FROM C
 GROUP BY C.PID 
)

SubItemList is a recursive SELECT that picks up the lowest child from the previous query, and allocates an incrementing item number to each child starting from 1:

SubItemList (PID, CID, ItemNum) AS ( 
 SELECT C.PID, C.ID, 1
 FROM C JOIN FirstItems FI ON (C.ID = FI.FirstCID)
 UNION ALL
 SELECT C.PID, C.ID, IL.ItemNum + 1
 FROM C JOIN SubItemList IL ON C.PID = IL.PID AND C.ID > CID
)

The trouble is it dups up and repeats a lot of the items, so ItemList filters it to just pick the max from each group:

ItemList (PID, CID, ItemNum) AS (
SELECT PID, CID, MAX(ItemNum)
 FROM SubItemList
 GROUP BY PID, CID
)

Now we have an ID list of parents with each of there children numbered from 1 to x:

PID CID ItemNum
----------- ----------- -----------
36 1 1
36 2 2
36 3 3
20 4 1
20 5 2

SubArrayList takes the children rows, recursively joins to the numbers list and starts appending all the descriptions to each other, starting with a single description:

SubArrayList (PID, CID, Array, ItemNum) AS ( 
 SELECT IL.PID, IL.CID, CAST(C.Description AS VARCHAR(MAX)), IL.ItemNum
 FROM ItemList IL JOIN C ON IL.CID = C.ID
 WHERE IL.ItemNum = 1
 UNION ALL
 SELECT IL.PID, IL.CID, AL.Array + ',' + CAST(C.Description AS VARCHAR(MAX)), IL.ItemNum
 FROM ItemList IL
 JOIN SubArrayList AL ON (IL.PID = AL.PID AND IL.ItemNum = AL.ItemNum + 1)
 JOIN C ON (IL.CID = C.ID)
)

The result is now:

PID CID Array ItemNum
----------- ----------- ----------------- -----------
36 1 Bob 1
20 4 Emily 1
20 5 Emily,Dave 2
36 2 Bob,Gary 2
36 3 Bob,Gary,Reginald 3

So all we need to do is to get rid of all the partly concatenated rows.

MaxItems simply grabs a list of parents and their highest item numbers, which makes the following query a bit simpler:

MaxItems (PID, MaxItem) AS ( 
 SELECT PID, MAX(ItemNum)
 FROM SubItemList
 GROUP BY PID 
)

ArrayList performs the final cull of the partly concatenated rows using the max item number aquired from the previous query:

ArrayList (PID, List) AS (
SELECT SAL.PID, SAL.Array
 FROM SubArrayList SAL 
 JOIN MaxItems MI ON (SAL.PID = MI.PID AND SAL.ItemNum = MI.MaxItem) 
)

And finally, all that remains is to query the result:

SELECT P.ID, P.Description, AL.List
 FROM ArrayList AL JOIN P ON P.ID = AL.PID
 ORDER BY P.ID

Answer 2

There are multiple ways to do it; the article below presents various solutions for this type of operation:

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

• Wayne: +1 as the most comprehensive answer and also for using set based instead of cursors. However, in the best interest of SO. I would suggest copying the text from the article in-case that is ever changed, deleted.

Answer 3

You need a function that when passed the foreign key that links the child table to the parent, queries the child table to build a cursor and then loops through it concatenating the required values. Function then returns that string. Function is then called as the fourth column of your query. Can't help you with the syntax etc as I don't use SQL-Server