I want SQL query for select multiple columns into one 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
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