在SQL Server中如何将列转换为行？

Question

表格：

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

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

我的表有不同的数据类型，col2是nvarchar类型，我该怎么做？

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

- jay

Answer 1

您可以使用 UNPIVOT 操作来获取您的结果。

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;

- bobs

Answer 2

使用：

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

问题的一部分是你需要将第二列变成相同的数据类型：

• CAST/CONVERT

- OMG Ponies

Answer 3

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

- manuzhang