How to update columns in a unique request of my query on Sql Server?

Question

I am trying to add a column to my query as follows,

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 request worked fine, but when I add "sf1" and "sf2", it gives me an error

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

how would I run the query mentioned above with the column that I want to update, for example, "sf1" and "sf2"

hopes for your suggestions

thanks

Answer 1

One way to create a 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.