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.
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
