问题描述
我有以下数据
+--------+|订单|+--------+|S1 ||S2 ||S3 ||S4 ||S5 ||S6 ||S7 ||S8 ||S9 ||S10 ||S11 ||S12 |+--------+
我需要按如下方式返回结果 - 一列包含五行:
+-----------------+|订单 |+---+|S1,S2,S3,S4,S5 ||S6,S7,S8,S9,S10 ||S11,S12 |+---+
没有什么可以分组或分隔成行的.所以我分配了一个row_number并对row_number做了mod 5.它几乎有效,但不完全.
这是我尝试过的:
;用 mycte 作为 (选择'S1' 作为订单联合全选'S2'联合全选'S3'联合全选'S4'联合全选'S5'联合全选'S6'联合全选'S7'联合全选'S8'联合全选'S9'联合全选'S10'联合全选'S11'联合全选'S12'),mycte2 为 (选择订单,ROW_NUMBER() over(order by orders) %5 作为 rownum来自mycte)选择不同的东西((SELECT ',' + mycte2.orders从 mycte2其中 t1.rownum=mycte2.rownumFOR XML 路径('')), 1, 1, '') 订单, 行数来自 mycte2 t1
结果是:
+-----------+--------+|订单|行数 |+-----------+--------+|S1,S3,S8 |1 ||S10,S4,S9 |2 ||S11,S5 |3 ||S12,S6 |4 ||S2,S7 |0 |+-----------+--------+
有人可以告诉我如何达到我想要的结果吗?
解决方案
怎么样
创建表T([订单] varchar(3));插入 T([订单])价值观('S1'),('S2'),('S3'),('S4'),('S5'),('S6'),('S7'),('S8'),('S9'),('S10'),('S11'),('S12');使用 CTE 作为(选择订单,(ROW_NUMBER() OVER(ORDER BY LEN(Orders)) - 1)/5 RN从T)SELECT STRING_AGG(订单, ',')从 CTE按RN分组RN 的命令;
或
SELECT STUFF((SELECT ',' + 订单从 CTE哪里 RN = TT.RNFOR XML 路径('')), 1, 1, '') 订单从 CTE TT按RN分组RN 的命令;
<块引用>
您可以使用 (SELECT 1) 而不是 LEN(Orders)
退货:
+-----------------+|订单 |+---+|S1,S2,S3,S4,S5 ||S6,S7,S8,S9,S10 ||S11,S12 |+---+
演示
I have the following data
+--------+ | orders | +--------+ | S1 | | S2 | | S3 | | S4 | | S5 | | S6 | | S7 | | S8 | | S9 | | S10 | | S11 | | S12 | +--------+
I am required to return the result as follows - fit five rows in one column:
+-----------------+ | Orders | +-----------------+ | S1,S2,S3,S4,S5 | | S6,S7,S8,S9,S10 | | S11,S12 | +-----------------+
There is nothing to group on or segregate these into rows. So I assigned a row_number and did mod 5 on the row_number. It almost works, but not quite.
Here is what I have tried:
;with mycte as ( select 'S1' as orders union all select 'S2' union all select 'S3' union all select 'S4' union all select 'S5' union all select 'S6' union all select 'S7' union all select 'S8' union all select 'S9' union all select 'S10' union all select 'S11' union all select 'S12' ) ,mycte2 as ( Select orders ,ROW_NUMBER() over( order by orders) %5 as rownum from mycte ) select distinct STUFF(( SELECT ',' + mycte2.orders FROM mycte2 where t1.rownum= mycte2.rownum FOR XML PATH('') ), 1, 1, '') orders , rownum from mycte2 t1
the result is :
+-----------+--------+ | orders | rownum | +-----------+--------+ | S1,S3,S8 | 1 | | S10,S4,S9 | 2 | | S11,S5 | 3 | | S12,S6 | 4 | | S2,S7 | 0 | +-----------+--------+
Can someone please show me how to get to my desired result?
解决方案
How about
CREATE TABLE T ([orders] varchar(3)); INSERT INTO T ([orders]) VALUES ('S1'), ('S2'), ('S3'), ('S4'), ('S5'), ('S6'), ('S7'), ('S8'), ('S9'), ('S10'), ('S11'), ('S12'); WITH CTE AS ( SELECT Orders, (ROW_NUMBER() OVER(ORDER BY LEN(Orders)) - 1) / 5 RN FROM T ) SELECT STRING_AGG(Orders, ',') FROM CTE GROUP BY RN ORDER BY RN;
OR
SELECT STUFF( ( SELECT ',' + Orders FROM CTE WHERE RN = TT.RN FOR XML PATH('') ), 1, 1, '' ) Orders FROM CTE TT GROUP BY RN ORDER BY RN;
You can use (SELECT 1) instead of LEN(Orders)
Returns:
+-----------------+ | Orders | +-----------------+ | S1,S2,S3,S4,S5 | | S6,S7,S8,S9,S10 | | S11,S12 | +-----------------+
Demo