在sql中将多行转换为单行 [英] Multiple rows into single row in sql
本文介绍了在sql中将多行转换为单行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我的数据如下:
名称
1
2
3
4
5
6
7
8
9
我需要以上数据:
ABC
1 2 3
4 5 6
7 8 9
每3行就必须中断。
解决方案
无需PIVOT
-ROW_NUMBER
和GROUP BY
的组合将完成这项工作:
WITH cteSortedData As
(
SELECT
名称,
ROW_NUMBER() OVER ( ORDER BY 名称) - 1 As RN
FROM
YourTable
)
SELECT
MAX( CASE RN% 3 WHEN 0 那么名称< span class =code-keyword> END ) As A,
MAX( CASE RN% 3 WHEN 1 THEN 名称 END )作为 B,
MAX( CASE RN% 3 WHEN 2 那么名称 END )< span class =code-keyword> As C
FROM
cteSortedData
GROUP BY
RN / 3
;
表达式RN / 3
使用整数除法,因此每组三行返回相同的值。
例如:0/3 = 0
;1/3 = 0
;2/3 = 0
;3/3 = 1
;4/3 = 1
; ...
%
是 Modulo运算符 [ ^ ],返回除法运算的剩余部分。
例如:0%3 = 0
;1%3 = 1
;2%3 = 2
;3%3 = 0
;4%3 = 1
; ...
I have data as below :
Name
1
2
3
4
5
6
7
8
9
I Require above data as:
A B C
1 2 3
4 5 6
7 8 9
For every 3 rows it has to break .
解决方案
No need toPIVOT
- a combination ofROW_NUMBER
andGROUP BY
will do the job:
WITH cteSortedData As ( SELECT Name, ROW_NUMBER() OVER (ORDER BY Name) - 1 As RN FROM YourTable ) SELECT MAX(CASE RN % 3 WHEN 0 THEN Name END) As A, MAX(CASE RN % 3 WHEN 1 THEN Name END) As B, MAX(CASE RN % 3 WHEN 2 THEN Name END) As C FROM cteSortedData GROUP BY RN / 3 ;
The expressionRN / 3
uses integer division, so each group of three rows returns the same value.
Eg:0 / 3 = 0
;1 / 3 = 0
;2 / 3 = 0
;3 / 3 = 1
;4 / 3 = 1
; ...
%
is the Modulo operator[^], which returns the remainder of a division operation.
Eg:0 % 3 = 0
;1 % 3 = 1
;2 % 3 = 2
;3 % 3 = 0
;4 % 3 = 1
; ...
这篇关于在sql中将多行转换为单行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文