我正在编写一个查询,希望结果显示在一列中。目前的结果显示如下:
Column1 Column2 column3
1 A CAT
我希望结果返回如下
Column1
1
A
CAT
我正在编写一个查询,希望结果显示在一列中。目前的结果显示如下:
Column1 Column2 column3
1 A CAT
我希望结果返回如下
Column1
1
A
CAT
SELECT Column1 FROM TableName
UNION ALL
SELECT Column2 FROM TableName
UNION ALL
SELECT Column3 FROM TableName
UNION
,而不是UNION ALL
。UNPIVOT
运算符来完成此操作。SELECT Column123
FROM
(
SELECT Column1, Column2, Column3
FROM TableName
) AS tmp
UNPIVOT
(
Column123 FOR ColumnAll IN (Column1, Column2, Column3)
) AS unpvt;
https://www.w3schools.com/sql/sql_union.asp
https://www.mssqltips.com/sqlservertip/3000/use-sql-servers-unpivot-operator-to-help-normalize-output/
Select
column1
from table
union all
select column2
from table
union all
select column3
from table
CREATE TABLE #t (Column1 NVARCHAR(25),Column2 NVARCHAR(25), column3 NVARCHAR(25))
INSERT INTO #t
SELECT '1','A','CAT'
SELECT
CASE a WHEN 1 THEN Column1 WHEN 2 THEN Column2 ELSE column3 END col
FROM #t, (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3) B
DROP TABLE #t
Unpivot
。 - uzi