使用 SQL 将单行拆分为多行 [英] Split a single row into multiple rows using SQL
本文介绍了使用 SQL 将单行拆分为多行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在尝试从一行生成多行，并且能够使用以下 SQL 来完成.但是我想知道是否有更好的方法来编写此 SQL，因为它很长并且不能应用于实时场景.谢谢.
I am trying to generate multiple rows from a single row and was able to do it using the below SQL. But I am wondering if there is a better way to write this SQL as its very long and can't apply to real-time scenarios. Thank you.
注意:- 我的查询将返回所需的输出，但这不是一种有效的方式来解决问题.我的实际输入有更多列，我不想写n"不.SQL 查询并为所有这些查询执行 UNION ALL.请提出更好的解决方案.谢谢.
Note:- My query would return the desired output but it is not an efficient way to solve the problem. My actual input has more columns and I don't want to write 'n' no. of SQL queries and do a UNION ALL for all of them. Please suggest a better solution. Thank you.
Input :-
A.Col2 A.Col4 A.Col6 B.Col1 B.Col2 B.Col3 B.Col4 B.Col5 B.Col6 B.Col7
300 301 302 100 9011 100 9002 100 9002 100
300 301 302 101 8101 95 2001 100 2001 100
300 301 302 102 8101 105 2001 110 2001 110
Desired output :-
100 300 9011 100
101 300 8101 95
102 300 8101 105
100 301 9002 100
101 301 2001 100
102 301 2001 110
100 302 9002 100
101 302 2001 100
102 302 2001 110
在我的示例中，我构建了总共 10 个字段的测试数据(前三个字段和其余七个字段.)对于第四列中的每个值，我必须构建三行四列，三行如下所示:
In my example I have built test data with total 10 fields (First three fields and the remaining seven fields.) For every value in fourth column I have to build three rows with four columns, the three rows would look like:
( 4th column, 1st column, 5th column, 6th column ),
( 4th column, 2nd column, 7th column, 8th column ),
( 4th column, 3rd column, 9th column, 10th column )
我当前的 SQL:
SELECT B.Col1 AS Constructioncode,
A.Col2 AS OccupancyCode,
B.Col2 AS MappingID,
B.Col3 AS DamageFactor
FROM
(
SELECT 0 AS Col1,300 AS Col2,0 AS Col3,301 AS Col4,0 AS Col5,302 AS Col6, 0 AS Col7
UNION ALL
SELECT 100 AS Col1,9011 AS Col2,100 AS Col3,9002 AS Col4,100 AS Col5,9002 AS Col6,100 AS Col7
UNION ALL
SELECT 101 AS Col1,8101 AS Col2,95 AS Col3,2001 AS Col4,100 AS Col5,2001 AS Col6,100 AS Col7
UNION ALL
SELECT 102 AS Col1,8101 AS Col2,105 AS Col3,2001 AS Col4,110 AS Col5,2001 AS Col6,110 AS Col7
) A
CROSS JOIN
(
SELECT 0 AS Col1,300 AS Col2,0 AS Col3,301 AS Col4,0 AS Col5,302 AS Col6, 0 AS Col7
UNION ALL
SELECT 100 AS Col1,9011 AS Col2,100 AS Col3,9002 AS Col4,100 AS Col5,9002 AS Col6,100 AS Col7
UNION ALL
SELECT 101 AS Col1,8101 AS Col2,95 AS Col3,2001 AS Col4,100 AS Col5,2001 AS Col6,100 AS Col7
UNION ALL
SELECT 102 AS Col1,8101 AS Col2,105 AS Col3,2001 AS Col4,110 AS Col5,2001 AS Col6,110 AS Col7
) B
WHERE B.Col1 > A.Col1
AND A.Col1 = 0
UNION ALL
SELECT B.Col1 AS Constructioncode,
A.Col4 AS OccupancyCode,
B.Col4 AS MappingID,
B.Col5 AS DamageFactor
FROM
(
SELECT 0 AS Col1,300 AS Col2,0 AS Col3,301 AS Col4,0 AS Col5,302 AS Col6, 0 AS Col7
UNION ALL
SELECT 100 AS Col1,9011 AS Col2,100 AS Col3,9002 AS Col4,100 AS Col5,9002 AS Col6,100 AS Col7
UNION ALL
SELECT 101 AS Col1,8101 AS Col2,95 AS Col3,2001 AS Col4,100 AS Col5,2001 AS Col6,100 AS Col7
UNION ALL
SELECT 102 AS Col1,8101 AS Col2,105 AS Col3,2001 AS Col4,110 AS Col5,2001 AS Col6,110 AS Col7
) A
CROSS JOIN
(
SELECT 0 AS Col1,300 AS Col2,0 AS Col3,301 AS Col4,0 AS Col5,302 AS Col6, 0 AS Col7
UNION ALL
SELECT 100 AS Col1,9011 AS Col2,100 AS Col3,9002 AS Col4,100 AS Col5,9002 AS Col6,100 AS Col7
UNION ALL
SELECT 101 AS Col1,8101 AS Col2,95 AS Col3,2001 AS Col4,100 AS Col5,2001 AS Col6,100 AS Col7
UNION ALL
SELECT 102 AS Col1,8101 AS Col2,105 AS Col3,2001 AS Col4,110 AS Col5,2001 AS Col6,110 AS Col7
) B
WHERE B.Col1 > A.Col1
AND A.Col1 = 0
UNION ALL
SELECT B.Col1 AS Constructioncode,
A.Col6 AS OccupancyCode,
B.Col6 AS MappingID,
B.Col7 AS DamageFactor
FROM
(
SELECT 0 AS Col1,300 AS Col2,0 AS Col3,301 AS Col4,0 AS Col5,302 AS Col6, 0 AS Col7
UNION ALL
SELECT 100 AS Col1,9011 AS Col2,100 AS Col3,9002 AS Col4,100 AS Col5,9002 AS Col6,100 AS Col7
UNION ALL
SELECT 101 AS Col1,8101 AS Col2,95 AS Col3,2001 AS Col4,100 AS Col5,2001 AS Col6,100 AS Col7
UNION ALL
SELECT 102 AS Col1,8101 AS Col2,105 AS Col3,2001 AS Col4,110 AS Col5,2001 AS Col6,110 AS Col7
) A
CROSS JOIN
(
SELECT 0 AS Col1,300 AS Col2,0 AS Col3,301 AS Col4,0 AS Col5,302 AS Col6, 0 AS Col7
UNION ALL
SELECT 100 AS Col1,9011 AS Col2,100 AS Col3,9002 AS Col4,100 AS Col5,9002 AS Col6,100 AS Col7
UNION ALL
SELECT 101 AS Col1,8101 AS Col2,95 AS Col3,2001 AS Col4,100 AS Col5,2001 AS Col6,100 AS Col7
UNION ALL
SELECT 102 AS Col1,8101 AS Col2,105 AS Col3,2001 AS Col4,110 AS Col5,2001 AS Col6,110 AS Col7
) B
WHERE B.Col1 > A.Col1
AND A.Col1 = 0
推荐答案
以下内容应该可以解决问题.我只通过一次数据就完成了.
The following should do the trick. And I does it with a single pass over the data.
IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL
DROP TABLE #TestData;
CREATE TABLE #TestData (
A_Col2 INT,
A_Col4 INT,
A_Col6 INT,
B_Col1 INT,
B_Col2 INT,
B_Col3 INT,
B_Col4 INT,
B_Col5 INT,
B_Col6 INT,
B_Col7 INT
);
INSERT #TestData (A_Col2, A_Col4, A_Col6, B_Col1, B_Col2, B_Col3, B_Col4, B_Col5, B_Col6, B_Col7) VALUES
(300, 301, 302, 100, 9011, 100, 9002, 100, 9002, 100),
(300, 301, 302, 101, 8101, 95, 2001, 100, 2001, 100),
(300, 301, 302, 102, 8101, 105, 2001, 110, 2001, 110);
--====================================================================
SELECT
Constructioncode = td.B_Col1,
ab.OccupancyCode,
ab.MappingID,
ab.DamageFactor
FROM
#TestData td
CROSS APPLY ( VALUES
(td.A_Col2, td.B_Col2, td.B_Col3),
(td.A_Col4, td.B_Col4, td.B_Col5),
(td.A_Col6, td.B_Col6, td.B_Col7)
) ab (OccupancyCode, MappingID, DamageFactor)
ORDER BY
ab.OccupancyCode,
td.B_Col1;
结果...
Constructioncode OccupancyCode MappingID DamageFactor
---------------- ------------- ----------- ------------
100 300 9011 100
101 300 8101 95
102 300 8101 105
100 301 9002 100
101 301 2001 100
102 301 2001 110
100 302 9002 100
101 302 2001 100
102 302 2001 110
这篇关于使用 SQL 将单行拆分为多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
