How can i use pivot ?

Question

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

Answer 1

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.