CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008pivot
jay
jay

Reputation: 3879

column to row in sql server?

Table:

CREATE TABLE Table1 (
  col1 INT, 
  col2 nvarchar(10), 
  col3 INT, 
  col4 INT
);

INSERT INTO Table1 
  (col1, col2, col3, col4) 
VALUES 
  (1, 'welcome', 3, 4);

My table have different data type , col2 is nvarchar h can i do this ...

result:

col    value
---------------
col1   1
col2   welcome
col3   3
col4   4

Upvotes: 5

Views: 22282

Answers (3)

manuzhang
manuzhang

Reputation: 3025

with rows(n)
as
(
  select 1 
  union all
  select n + 1
  from rows
  where n + 1 <= 4 
 )
   select case n 
          when 1 then 'col1'
          when 2 then 'col2'
          when 3 then 'col3'
          when 4 then 'col4'
          end as col,
          case n 
          when 1 then col1
          when 2 then col2
          when 3 then col3
          when 4 then col4
          end as value
  from
 (
  select cast (col1 as varchar) col1,
         col2, 
         cast (col3 as varchar) col3,
         cast (col4 as varchar) col4,
         n
  from table1, rows
  ) x

Upvotes: 0

bobs
bobs

Reputation: 22184

You can use the UNPIVOT operation to get your results

SELECT col, value
FROM 
   (SELECT CAST(col1 AS VARCHAR) AS col1, CAST(col2 AS VARCHAR) AS col2,
        CAST(col3 AS VARCHAR) AS col3, CAST(col4 AS VARCHAR) AS col4
   FROM Table1) p
UNPIVOT
   (value FOR col IN 
      (col1, col2, col3, col4)
) AS unpvt;

Upvotes: 14

OMG Ponies
OMG Ponies

Reputation: 332541

Use:

SELECT 'col1' AS col,
        CAST(t1.col1 AS NVARCHAR(10)) AS value
   FROM TABLE_1 t1
UNION ALL
SELECT 'col2' AS col,
        t2.col2 AS value
   FROM TABLE_1 t2
UNION ALL
SELECT 'col3' AS col,
       CAST(t3.col3 AS NVARCHAR(10)) AS value
  FROM TABLE_1 t3
UNION ALL
SELECT 'col4' AS col,
       CAST(t4.col4 AS NVARCHAR(10)) AS value
  FROM TABLE_1 t4

Part of the problem is that you need to make the second column the same data type:

Upvotes: 1

Related Questions