Group by Row to column in SQL Server table

xtupzzrd 于 2023-04-19 发布在 SQL Server

关注(0)|答案(3)|浏览(139)

In SQL Server, I have maintained the following details:
| S.No | Parent_ID | Child_ID | QTY |
| ------------ | ------------ | ------------ | ------------ |
| 1 | NULL | 1 | 1 |
| 2 | 1 | 2 | 2 |
| 3 | 2 | 3 | 4 |
| 4 | NULL | 5 | 2 |
| 5 | 5 | 6 | 5 |
| 6 | 6 | 7 | 1 |
| 7 | NULL | 1 | 2 |
| 8 | 1 | 7 | 3 |
| 9 | 7 | 9 | 1 |

Required table like below.

QTY	Child_ID	Groups
1	1	1
2	2	1,2
4	3	1,2,3
2	5	5
5	6	5,6
1	7	5,6,7
2	1	1
3	7	1,7
1	9	1,7,9

I tried with this code, but I am getting a recursion error:

WITH GroupTable (Qty, Child_ID, Parent_ID,  Groups) AS
(
    SELECT 
        S.Qty, S.Child_ID, S.Parent_ID, 
        CONVERT(varchar(100), S.Child_ID) AS path
    FROM 
        tabel1 AS S 
    UNION ALL
    SELECT 
        S.Qty, S.Child_ID, p.Parent_ID,  
        CONVERT(varchar(100), (RTRIM(p.Groups) + ',' + 
                              CONVERT(varchar(100), S.Child_ID)))
    FROM 
        GroupTable AS p 
    JOIN 
        tabel1 AS S ON S.Parent_ID = p.Child_ID
)
SELECT
    Qty, Child_ID, Groups 
FROM
    GroupTable 
WHERE
    Parent_ID IS NULL

I'm getting this error:

Msg 530, Level 16, State 1, Line 1 The statement terminated.
The maximum recursion 100 has been exhausted before statement completion

sql-server

来源：https://stackoverflow.com/questions/76051048/group-by-row-to-column-in-sql-server-table

3条答案

按热度按时间

kkih6yb81#

I don't understood your question but as per the error you provided you can set maximum recursion in SQL Server. In the following example I set for 500 maximum is up to 32,767 depending on SQL Server version.

SELECT
    Qty, Child_ID, Groups 
FROM
    GroupTable 
WHERE
    Parent_ID IS NULL

OPTION (MAXRECURSION 500)

赞(0）回复(0）举报 2023-04-19

yhived7q2#

For your maximum recursion problem, you can use:

FROM GroupTable
  option (maxrecursion 0)

赞(0）回复(0）举报 2023-04-19

aelbi1ox3#

You are hitting the MAXRECURSION cap because you are getting more rows, then you are expecting - probably because your bossiness logic or/and data is not well defined.

For example, for child 2 you need parent 1 but in your base data you have two records for parent 1 , so you have more rows after the first iteration of the CTE:

But in your final result, you are want the data from this group only:

With your real data you may find more cases like this, and it will be difficult to filter all of them in the final SELECT or you just may have wrong data.

So, you need to think how to define this groups, because currently is not possible.

赞(0）回复(0）举报 2023-04-19

我来回答

Group by Row to column in SQL Server table

3条答案

相关问题

热门标签

最新问答