SQL Server表结果转换为SQL Server 2005中的数组 [英] SQL Server table result to array in SQL Server 2005
问题描述
谢谢。
首先，我并不是说我想将数组转换为表。我可以的容易：）
First off, I do not mean I want to convert an array into a table. I can do that. Easy :)
我想做相反的事情。
我在 n行在另一个表中与此相关的行。我希望输出该行，然后输出该行的单个列中的数据，例如 Children ，其链接行以 array （或字符串）我可以将其解释为带有前端代码的数组。）
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).
就像这样：
ID TITLE DESCRIPTION CHILDREN
--------------------------------------------------------
36 Blah Blah Blah ['Bob','Gary','Reginald']
20 Pah Pah Pah ['Emily','Dave']
你看到了吗？
我知道，这可能不是最好的方法。但这是针对特定用途，在这里无法解释。
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.
再次感谢。
推荐答案
您实际上可以在一个CTE选择查询中完成全部操作，而无需使用任何功能。方法如下：
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));
（我使用P和C来节省输入时间！）
(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');
最后，CTE表达式为：
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
结果：
ID Description List
-- -------------- --------
20 Pah Pah Emily,Dave
36 Blah Blah Bob,Gary,Reginald
为解释这里发生的事情，我将介绍CTE的每个部分及其作用。
To explain what's going on here, I'll describe each part of the CTE and what it does.
FirstItems 外观在所有ch ildren，并找到每个父组中最低的ID作为下一个递归SELECT的锚点：
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 是一个递归SELECT，它从上一个查询中选取最低的子项，并为每个子项分配一个递增的项号从1开始：
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
)
麻烦的是它重复了很多东西，所以重复很多 ItemList 对其进行过滤，以仅从每个组中选择最大值：
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
)
现在我们有了父母的ID列表，其中每个孩子的编号从1到x：
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 接收子行，递归地连接到数字列表，并开始将所有描述彼此附加，从一个描述开始：
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)
)
现在的结果是：
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 使用从上一个查询中获得的最大项目编号对部分串联的行执行最终剔除：
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
这篇关于SQL Server表结果转换为SQL Server 2005中的数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
