取消旋转多列 [英] Unpivoting multiple columns

问题描述

我在 SQL Server 2014 中有一个名为 anotes 的表，其中包含以下数据

I have a table in SQL Server 2014 called anotes with the following data

我想将此数据添加到另一个名为 final 的表中

and I want to add this data into another table named final as

ID      Notes      NoteDate

随着 text1, text2, text3, text4 进入最终表格中的 Notes 列并且 Notedate1,notedate2,notedate3,notedate4 进入进入 Notedate 列.

With text1, text2, text3, text4 going into the Notes column in the final table and Notedate1,notedate2,notedate3,notedate4 going into Notedate column.

我首先尝试使用注释对数据进行反透视:

I tried unpivoting the data with notes first as:

select createdid, temp
from (select createdid,text1,text2,text3,text4 from anotes) p
unpivot
(temp for note in(text1,text2,text3,text4)) as unpvt
order by createdid

这给了我正确的结果:

然后对于日期部分，我使用了另一个逆透视查询:

and then for the dates part I used another unpivot query:

select createdid,temp2
from (select createdid,notedate1,notedate2,notedate3,notedate4 from anotes) p
unpivot (temp2 for notedate in(notedate1,notedate2,notedate3,notedate4)) as unpvt2

这也给了我正确的结果:

which also gives me proper results:

现在我想将此数据添加到我的最终表格中.

Now I want to add this data into my final table.

我尝试了以下查询，结果是交叉联接:(

and I tried the following query and it results into a cross join :(

select a.createdid, a.temp, b.temp2
from (select createdid, temp
      from (select createdid,text1,text2,text3,text4 from anotes) p
      unpivot
      (temp for note in(text1,text2,text3,text4)) as unpvt) a inner join (select createdid,temp2
from (select createdid,notedate1,notedate2,notedate3,notedate4 from anotes) p
unpivot (temp2 for notedate in(notedate1,notedate2,notedate3,notedate4)) as unpvt) b on a.createdid=b.createdid

输出如下:

有什么办法可以同时取消两个列的旋转?

Is there any way where I can unpivot both the columns at the same time?

或者使用两个选择查询将该数据添加到我的最终表格中?

Or use two select queries to add that data into my final table?

提前致谢！

推荐答案

我想说最简洁，也可能是最有效的反透视多个列的方法是使用 CROSS APPLY 以及 表值构造函数:

I would say the most concise, and probably most efficient way to unpivot multiple columns is to use CROSS APPLY along with a table valued constructor:

SELECT  t.CreatedID, upvt.Text, upvt.NoteDate
FROM    anotes t
        CROSS APPLY
        (VALUES
            (Text1, NoteDate1),
            (Text2, NoteDate2),
            (Text3, NoteDate3),
            (Text4, NoteDate4),
            (Text5, NoteDate5),
            (Text6, NoteDate6),
            (Text7, NoteDate7)
        ) upvt (Text, NoteDate);

SQL Fiddle 上的简化示例

附录

我发现这个概念很难解释，但我会尝试.表值构造器只是一种动态定义表的方式，所以

I find the concept quite a hard one to explain, but I'll try. A table valued constuctor is simply a way of defining a table on the fly, so

SELECT  *
FROM    (VALUES (1, 1), (2, 2)) t (a, b);

将创建一个带有数据的别名 t 的表:

Will Create a table with Alias t with data:

a   b
------
1   1
2   2

因此，当您在 APPLY 中使用它时，您可以访问所有外部列，因此只需使用正确的值对(即 text1 和 date1)定义构造的表.

So when you use it inside the APPLY you have access to all the outer columns, so it is just a matter of defining your constructed tables with the correct pairs of values (i.e. text1 with date1).

这篇关于取消旋转多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！