如何将单个整数值列分成 n 列？

Question

I have a SQL Server table table_name like:

col1   
1
2
3
4
5
6
7
8
9
.
.
.
N

我希望能够得到以下输出：

col-1 | col-2 | col-3 | col-4 |col-5
-------------------------------
1     | 2     | 3     | 4     |5
6     | 7     | 8     | 9     |10
11    | 12    | 13    | 14    |15

如何从SQL Server查询的输出中获取结果。

- sankar d

---

你正在使用哪个版本的SQL Server？如果是2016，那么有一个STRING_SPLIT函数。 - Keith

或许透视表能在这里帮上忙？ - Mat

谢谢，我正在使用SQL Server 2012。 - sankar d

Answer 1

另一个选择是使用动态透视表。

这将创建N列。

示例

Declare @nCols int  = 5

Declare @SQL varchar(max) = '
Declare @nCols int = '+str(@nCols,5)+'
Select *
 From (
        Select RowNr = ((row_number() over (order by col1)-1)/@nCols)+1
              ,ColNr = concat(''col-'',isnull(nullif((row_number() over (order by col1))%@nCols,0),@nCols))
              ,Value = col1
         From YourTable
      ) A
 Pivot (max(Value) For ColNr in (' + Stuff((Select Top (@nCols) ','+QuoteName(concat('col-',Row_Number() Over (Order By (Select NULL)))) From  YourTable For XML Path('')) ,1,1,'') + ') ) p'
Exec(@SQL);
--Print @SQL

返回值

如果 @nCols = 3，则结果为：

编辑一个完全参数驱动的版本

您可以提供以下信息：

1. 列数 @NbrCols（与上述相同）
2. 源 @FromSrc，即表名或 SQL 字符串，例如 (Select ...)
3. 要枢轴的列名 @ColName
4. 列前缀 @ColPrfx，例如 'Col-' 或者 ''

---

Declare @NbrCols int  = 5
Declare @FromSrc varchar(max) = 'YourTable'  -- Or SQL '(Select col ...)'
Declare @ColName varchar(100) = 'col1'
Declare @ColPrfx varchar(100) = 'Col-'

Declare @SQL varchar(max) = '
Declare @NbrCols int = '+str(@NbrCols,5)+'
Select *
 From (
        Select Row = ((row_number() over (order by '+quotename(@ColName)+')-1)/@NbrCols)+1
              ,Col = concat('''+@ColPrfx+''',isnull(nullif((row_number() over (order by '+quotename(@ColName)+'))%@NbrCols,0),@NbrCols))
              ,Val = '+quotename(@ColName)+'
         From '+@FromSrc+' A1
      ) A
 Pivot (max(Val) For Col in (' + Stuff((Select Top (@NbrCols) ','+QuoteName(concat(@ColPrfx,Row_Number() Over (Order By (Select NULL)))) From master..spt_values For XML Path('')) ,1,1,'') + ') ) p'
Exec(@SQL);
--Print @SQL

- John Cappelletti

---

这可能是迄今为止最好的答案，因为OP要求n列。我投了赞成票。 - Zohar Peled

我真的很喜欢这个答案，所以加一，不过我建议可以通过稍微修改查询语句来简化RowNr，而不是使用窗口函数: dbfiddle.uk演示 rextester演示，没有窗口spool的执行计划更简单，尽管我还没有测试它在处理较大数据集时是否会有任何显著的性能差异。 - SqlZim

@SqlZim，我正在看您的解决方案，也在考虑同样的事情。马上会进行更新。 - John Cappelletti

Answer 2

使用基于 row_number() 分组的条件聚合，并使用 % 取模 进行列定位：

测试设置：

select n into dbo.numbers from (values 
  (1), (2), (3), (4), (5), (6), (7), (8), (9),(10)
,(11),(12),(13),(14),(15),(16),(17),(18),(19),(20)
) t(n)
delete from dbo.numbers where n in (12,13,17);

查询：

select 
    col1 = sum(case when rn%5=0 then n end)
  , col2 = sum(case when rn%5=1 then n end)
  , col3 = sum(case when rn%5=2 then n end)
  , col4 = sum(case when rn%5=3 then n end)
  , col5 = sum(case when rn%5=4 then n end)
from (
  select n, rn = row_number() over (order by n)-1
  from dbo.numbers
  ) t
group by rn/5;

rextester演示：http://rextester.com/UHKY16981

返回：

+------+------+------+------+------+
| col1 | col2 | col3 | col4 | col5 |
+------+------+------+------+------+
|    1 |    2 | 3    | 4    | 5    |
|    6 |    7 | 8    | 9    | 10   |
|   11 |   14 | 15   | 16   | 18   |
|   19 |   20 | NULL | NULL | NULL |
+------+------+------+------+------+

使用 pivot() 而不是条件聚合的相同概念返回相同的结果。

select 
    col1 = [0]
  , col2 = [1]
  , col3 = [2]
  , col4 = [3]
  , col5 = [4]
from (
  select n
    , rn  = (row_number() over (order by n)-1)%5
    , grp = (row_number() over (order by n)-1)/5
  from dbo.numbers
  ) t
pivot (sum(n) for rn in ([0],[1],[2],[3],[4])) p;

- SqlZim

---

这比我的答案更好。我开始使用条件聚合，但不知何故没有完全弄对，所以我选择了另一种解决方案。 - Zohar Peled

Answer 3

假设您的表不仅仅是保存了从1到n的连续数字（如果是这样，Gordon的答案更好），以下是一种方法：

首先，创建并填充示例表格（请在以后的问题中保存此步骤）。

SELECT TOP 100 IDENTITY(int,1,1) AS col1
    INTO table_name
    FROM sys.objects s1       
    CROSS JOIN sys.objects s2 


DELETE
FROM table_name
WHERE col1 IN
(
    SELECT TOP 67 col1
    FROM table_name
    ORDER BY NEWID()
)

"table_name现在包含1到100之间的33个随机数字。使用几个常用的表达式来获取列号和行号："

;With Cols as
(
    SELECT  col1, 
            ROW_NUMBER() OVER(ORDER BY col1) % 5 ColNumber
    FROM table_name
), RowsAndCols as
(
    SELECT col1, ColNumber, ROW_NUMBER() OVER(PARTITION BY ColNumber ORDER BY col1) As RowNumber
    FROM Cols
)

而且查询：

SELECT c1.col1, c2.col1 As col2, c3.col1 As col3, c4.col1 As col4, c5.col1 As col5
FROM RowsAndCols c1
LEFT JOIN RowsAndCols c2 ON c1.RowNumber = c2.RowNumber AND c2.ColNumber = 2
LEFT JOIN RowsAndCols c3 ON c1.RowNumber = c3.RowNumber AND c3.ColNumber = 3
LEFT JOIN RowsAndCols c4 ON c1.RowNumber = c4.RowNumber AND c4.ColNumber = 4
LEFT JOIN RowsAndCols c5 ON c1.RowNumber = c5.RowNumber AND c5.ColNumber = 0
WHERE c1.ColNumber = 1

请注意，我使用了左连接，因此如果行数不是5的倍数，则在最后一行的最后几列中会得到null值。

请参见 rexteser上的实时演示。

- Zohar Peled

---

感谢这个查询，它对我来说很好用。再次感谢@Zohar Peled。 - sankar d

很高兴能帮到你 :-).现在你只需要选择想要使用的答案并将其标记为已采纳，这样其他人就会知道问题已经解决了。 - Zohar Peled

Answer 4

如果你想要输出，甚至不需要从表格开始：

with cte as (
      select 1 as col1, 2 as col2, 3 as col3, 4 as col4, 5 as col5
      union all
      select 5 + col1, 5 + col2, 5 + col3, 5 + col4, 5 + col5
      from cte
      where 5 + col1 <= 15
     )
select *
from cte;

- Gordon Linoff

---

请在Col4后面加上逗号。 - Gagan Sharma