我如何使用枢轴? [英] How can i use pivot?

问题描述

我正在尝试使用桌子上的枢轴来旋转(转置)桌子，但我不知道该怎么做.

I am trying to use pivot on a table to rotate(Transpose) the table but i m not getting how to do that.

我想知道它实际上是如何工作的.我发现了很多 egs，但我无法理解它们是如何工作的.

I want to know how it actually works. i found many egs but i was not able to understant how those work.

检查这个，例如

这是如何工作的，我只想将我的行转换为 cols 并将 cols 转换为行假设我在第 1 天第 2 天有 31 个列，直到第 31 天和一个外键 EmpId现在我想旋转 day1 day2

How tis is working, i just want to transpose my rows into cols and cols into rows Say i have 31 cols for day1 day2 and so on up to day 31 and a foreign key EmpId now i want to rotate day1 day2

EmpId Day1 Day2 Day3
----------
1       A   P     P
2       P   P     p

这里我想像这样旋转桌子

Here i want to rotate table like this

Exp   1  2  3 as EmpId
----------
Day1  A  P  ....
Day2  P  P  ...
Day3  P  P  ...

等等

推荐答案

我查看了您的技术帖子 here 我发现您不想使用 PIVOT，因为它只返回聚合结果.UNPIVOT 将使您非常接近您要查找的内容，然后只需对表的每一列进行联接即可.我用你在下面提供的数据做了一个例子:

I checked your tech posting here and I found that you do not want to use PIVOT because it only returns aggregate results. UNPIVOT will get you very close to what you are looking for and then just do a join on the table for each column. I made an example with the data you gave below:

----Create the table and insert values as portrayed in the previous example.
CREATE TABLE pvt (EmpId int, Day1 char(1), Day2 char(1), Day3 char(1));
CREATE TABLE pvt2 (EmpId int, DayNum CHAR(4), DayNums CHAR(4));
GO
INSERT INTO pvt VALUES (1,'A','P','P');
INSERT INTO pvt VALUES (2,'P','P','P');
GO
--Unpivot the table.
INSERT INTO pvt2
SELECT EmpId,DayNum, DayNums
FROM 
   (SELECT EmpId, Day1, Day2, Day3
   FROM pvt) p
UNPIVOT
   (DayNums FOR DayNum IN 
      (Day1,Day2,Day3)
)AS unpvt;
GO
SELECT
 a.DayNum,
 a.DayNums,
    b.DayNums
FROM pvt2 a
 JOIN pvt2 b ON a.DayNum = b.DayNum
  AND a.EmpId=1
  AND b.EmpId = 2

运行上面的代码会返回:

Running the above code will return:

Exp   1  2  3 as EmpId
----------
Day1  A  P  ....
Day2  P  P  ...
Day3  P  P  ...

我所知道的唯一其他解决方案是动态 SQL，它速度较慢且存在危险.但是，如果您只运行一次代码，那也可以.

The only other solution I know of is dynamic SQL and that is slower and has it's dangers. However, if you are running the code one time that could also work.

这篇关于我如何使用枢轴?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！