sql server中的列到行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3956345/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-01 07:58:29 来源:igfitidea点击:
column to row in sql server?
提问by jay
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 ...
我的表有不同的数据类型，col2 是 nvarchar h 我可以这样做吗...
result:
结果：
col value
---------------
col1 1
col2 welcome
col3 3
col4 4
回答by bobs
You can use the UNPIVOToperation to get your results
您可以使用该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;
回答by OMG Ponies
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:
部分问题是您需要使第二列具有相同的数据类型：
回答by manuzhang
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
