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

### 本文来自：IT宝库（https://www.itbaoku.cn）

tis的工作原理，我只想将我的行分为cols和cols cols行 说我有31天的第2天的Col，等待第31天和外国钥匙Empid 现在我想旋转第1天

```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.

Check this, e.g.

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.