I want SQL query for select multiple columns into one

Question

I have one single table and in that table there are three columns company1, company2, company3.

Below is my table data :

Company1 Company2 Company3
ABC 
Test1 Test3 Test5
Test2 Test4 Test6 
testing testing2 

And I want to combine these columns into single column with serial number like below :

SrNo CompanyName
1 ABC 
2 Test1 
3 Test2 
4 testing 
5 Test3
6 Test4
7 testing2 
8 Test5
9 Test6

Answer 1

One way to get the desired result is with UNION ALL of a SELECT query for each company column. Assign the serial number value in order by source column and company name using ROW_NUMBER in the outer SELECT.

INSERT INTO #Company(Company1, Company2, Company3)
VALUES
 ('ABC', NULL, NULL) 
 ,('Test1', 'Test3', 'Test5')
 ,('Test2', 'Test4', 'Test6') 
 ,('testing', 'testing2', NULL);
SELECT
 ROW_NUMBER() OVER(ORDER BY SourceColumn, CompanyName) AS SrNo
 , CompanyName
FROM (
 SELECT 1, Company1
 FROM #Company
 WHERE Company1 IS NOT NULL
 UNION ALL
 SELECT 2, Company2
 FROM #Company
 WHERE Company2 IS NOT NULL
 UNION ALL
 SELECT 3, Company3
 FROM #Company
 WHERE Company3 IS NOT NULL
 ) AS Companies(SourceColumn, CompanyName)
ORDER BY SrNo;
GO

Answer 2

I've always liked using UNPIVOT for these types of operations. Assuming you have some sort of ID column on the source table, this also works.

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 

Answer 3

You could use a union of three queries like something like this

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

To get the serial number you could use row_number like something like this

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