本文是小编为大家收集整理的关于如何使用枢轴?的处理/解决方法，可以参考本文帮助大家快速定位并解决问题，中文翻译不准确的可切换到English标签页查看源文。
问题描述
我试图在桌子上使用枢轴旋转(转置)表，但我没有得到如何做.
我想知道它实际上是如何工作的. 我找到了许多EG，但我无法忍受那些工作方式.
tis的工作原理，我只想将我的行分为cols和cols cols行
说我有31天的第2天的Col，等待第31天和外国钥匙Empid
现在我想旋转第1天 在这里我想像这样旋转桌子 等等 我检查了您的技术发布在这里发现您不想使用枢轴，因为它仅返回聚合结果. Undivot将使您非常接近您想要的内容，然后为每列在桌子上加入一个.我以您提供的数据为例: 运行上述代码将返回: 我知道的唯一其他解决方案是动态的SQL，这较慢并且存在危险.但是，如果您一次运行的代码也可以工作.EmpId Day1 Day2 Day3
----------
1 A P P
2 P P p
Exp 1 2 3 as EmpId
----------
Day1 A P ....
Day2 P P ...
Day3 P P ...
推荐答案
----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
Exp 1 2 3 as EmpId
----------
Day1 A P ....
Day2 P P ...
Day3 P P ...
问题描述
I am trying to use pivot on a table to rotate(Transpose) the table but i m not getting how to do that.
I want to know how it actually works. i found many egs but i was not able to understant how those work.
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 ...
and so on
推荐答案
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 ...
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.
更多相关问答
联系站长