我希望SQL查询选择多个列为一个
我希望SQL查询选择多个列为一个
提问于 2020-07-17 06:51:03
我只有一个表，在那个表中有三列company1、company2、company3。
以下是我的表格数据：
Company1 Company2 Company3
ABC
Test1 Test3 Test5
Test2 Test4 Test6
testing testing2 我想将这些列合并成具有序列号的单个列，如下所示：
SrNo CompanyName
1 ABC
2 Test1
3 Test2
4 testing
5 Test3
6 Test4
7 testing2
8 Test5
9 Test6回答 2
Database Administration用户
发布于 2020-07-17 13:14:36
我一直喜欢用UNPIVOT进行这类手术。假设源表上有某种ID列，这也是可行的。
DECLARE @Company TABLE
(
PKID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
, Company1 NVARCHAR(20) NULL
, Company2 NVARCHAR(20) NULL
, Company3 NVARCHAR(20) NULL
)
INSERT INTO @Company
(Company1, Company2, Company3)
VALUES
('ABC', NULL, NULL)
,('Test1', 'Test3', 'Test5')
,('Test2', 'Test4', 'Test6')
,('testing', 'testing2', NULL);
SELECT SrNo = ROW_NUMBER() OVER (ORDER BY PKID, CompanySrcCol)
, CompanyName
FROM @Company AS C
UNPIVOT (CompanyName FOR CompanySrcCol IN (Company1, Company2, Company3)) AS unpvt
ORDER BY PKID
, CompanySrcCol Database Administration用户
发布于 2020-07-17 11:41:42
您可以使用三种查询的联合使用，比如这样的
SELECT 1 AS tableNro, Company1 AS CompanyName
FROM yourTable
UNION ALL
SELECT 2 AS tableNro, Company2
FROM yourTable
UNION ALL
SELECT 3 AS tableNro, Company3
FROM yourTable要获得序列号，您可以像这样使用row_number
SELECT ROW_NUMBER() OVER(ORDER BY (x.tablenro, CompanyName)) AS SrNo, x.CompanyName
FROM (
SELECT 1 AS tableNro, Company1 AS CompanyName
FROM yourTable
UNION ALL
SELECT 2 AS tableNro, Company2
FROM yourTable
UNION ALL
SELECT 3 AS tableNro, Company3
FROM yourTable
) x页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://dba.stackexchange.com/questions/271123
复制相关文章
相似问题
腾讯云开发者
