問題描述

我有以下數據

+--------+|訂單|+--------+|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

這篇關于五列到一行的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！