五列到一行 [英] Five Columns to a single row

问题描述

我有以下数据

+--------+|订单|+--------+|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

这篇关于五列到一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！