CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user1599615
user1599615

Reputation:

Column to rows, rows to rows in SQL Server

I have table like this 

      id  |    vname1    |   vname2 | vname3     
      1   |     vala     |   valb   | valc

I want this to convert like this 

     id | vname  | vals 
     1  | vname1 | vala
     1  | vname2 | valb
     1  | vname3 | valc

I thought about pivoting but here I think is not the case

Upvotes: 0

Views: 40

Answers (2)

Ranadip Dutta
Ranadip Dutta

Reputation: 9133

You can use the UNPIVOT function to convert the columns into rows:

Sample Example:

select Id,
  indicatorname,
from yourtable
unpivot
(
  indicatorvalue
  for indicatorname in (Indicator1, Indicator2, Indicator3)
) unpiv;

Link for reference: UnPivot

Upvotes: 1

jarlh
jarlh

Reputation: 44766

Do a UNION ALL, with one SELECT for each vname column:

select id, 'vname1' as vname, vname1 as vals from tablename
union all
select id, 'vname2' as vname, vname2 as vals from tablename
union all
select id, 'vname3' as vname, vname3 as vals from tablename

Upvotes: 1

Related Questions