每隔一列转置或取消旋转 [英] Transpose or unpivot every other column
问题描述
基本上我想要做的是将每隔一列转置为一行,旁边有以下列数据.
Basically what I am trying to do is transpose every other column to a row with the following columns data beside it.
源可以只有两列,最多一千和几百万行.我只是在每行的基础上这样做.我曾尝试执行UNION ALL",但是在处理大表中的数百列时,这非常慢.
The source can have only two columns up to one-thousand and millions of rows. I am only doing this on a per-row basis. I have tried doing a "UNION ALL" however this is incredibly slow when dealing with hundreds of columns in a large table.
可能的解决方案是使用 UNPIVOT,尽管我能找到的每个示例都在做与我想要的不同的事情.
Possible solutions are using UNPIVOT although every example I can find is doing something different from what I want.
示例:查询前
Columns...
Apple | Apple Data | Banana | Banana Data | Cranberry | Cranberry Data | ....
Data Returned...
IOWA_PL_RLA | 1 | IOWA_PL_MATH | 1 | IOWA_SS_RLA 684 .....
示例:查询后
MeasureID Score
CRCT_PL_RLA | 1
CRCT_PL_MATH | 1
CRCT_SS_RLA |684
.....
希望我已经对自己进行了足够的解释,以便有人可以提供帮助,因为我在 T-SQL 方面经验有限.如果需要更多信息,请告诉我.
Hopefully I have explained myself enough that someone can help as I am only moderately experienced in T-SQL. If more information is needed please let me know.
推荐答案
如果您使用的是 SQL Server 2008,我认为您应该考虑使用 CROSS APPLY (VALUES) 技术:
If you are using SQL Server 2008, I think you should look into using the CROSS APPLY (VALUES) technique:
http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/
基本上是这样的:
SELECT
x.NewColumn1,
x.NewColumn2
FROM YourTable t
CROSS APPLY (
VALUES
(t.OldColumn1, t.OldColumn2),
(t.OldColumn3, t.OldColumn4),
...
) x (NewColumn1, NewColumn2);
这是一个可运行的示例:http://sqlfiddle.com/#!3/9a9d2/5/0
Here's a runnable example: http://sqlfiddle.com/#!3/9a9d2/5/0
这篇关于每隔一列转置或取消旋转的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!