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

http://stackoverflow.com/questions/11158017/column-conflicts-with-the-type-of-other-columns-in-the-unpivot-list — mohan111, Aug 20 '14 at 13:47
SELECT Name as [holiday_name] FROM tableMonth t UNPIVOT (ID for Name in (m1,m2,m3, cast(sf1 as decimal(18,16))as sf1,cast(sf2 as decimal(18,16))as sf2) u WHERE ID != 0.0 and D_No ='0700' — user3664724, Aug 20 '14 at 13:53
@mohani am not using casting in other column too so why should i use casting in it and it do not help me too is there any temp table created before where i mentioned these column name ? — user3664724, Aug 21 '14 at 07:42
Can you give a SQL Fiddle, with example of what you want to achieve? — Code Different, Aug 25 '14 at 21:52

score 2 · Answer 1 · answered Sep 24 '14 at 19:36

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.

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

1 Answers1

One way of creating New column during UNPIVOT