How to update columns in unpivot query my Query attached Sql Server?

Question

i am trying to add column in my query it is as under,

SELECT Name as [holiday_name] FROM tableMonth t UNPIVOT (ID for Name in (m1,m2,m3,sf1,sf2)) u WHERE ID != 0.0 and D_No ='0700'

this query was running fine but when i add "sf1" and "sf2" it give me error

"The type of column "sf1" conflicts with the type of other columns specified in the UNPIVOT list."

how would i run query mentioned above with column i want to update like "sf1" and "sf2"

hopes for you suggestions

thanks

Answer 1

One way of creating New column during UNPIVOT

CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
    Emp3 int, Emp4 int, Emp5 int);
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO


    SELECT VendorID, newcolumn, Orders,
case when len(VendorID) > 1 then ' ' end newcolumn1 ,
case when len(VendorID) > 1 then ' ' end newcolumn2 
    FROM 
   (SELECT VendorID, emp1,emp2,emp3,emp4,emp5
   FROM pvt) p
UNPIVOT
   (Orders FOR newcolumn IN (emp1,emp2,emp3,emp4,emp5)
)AS unpvt;

I believe this is your requirement.