SQL Server table result to array in SQL Server 200

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.

Answer 1

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

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/

Answer 3

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