Sql-server – I want SQL query for select multiple columns into one

sql serversql-server-2008

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

Best Answer

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

Related Solutions

Sql-server – Need to divide single column into multiple columns

For the first case you can use ROW_NUMBER then PIVOT to do this using the modulo operator to divide into columns and the result of integer divsion by 5 to group into rows.

WITH CTE AS
(
SELECT C,
       (ROW_NUMBER() OVER (ORDER BY C) -1)%5 AS Col,
       (ROW_NUMBER() OVER (ORDER BY C) -1)/5 AS Row
FROM YourTable       
)
SELECT [0], [1], [2], [3], [4]
FROM CTE
PIVOT (MAX(C) FOR Col IN ([0], [1], [2], [3], [4])) AS Pvt
ORDER BY Row

SQL Fiddle

For the second case Oracle has a straight forward syntax to achieve this with PIVOT. SQL Server doesn't but you can do this as below.

WITH CTE AS
(
SELECT *,
       (ROW_NUMBER() OVER (ORDER BY Column1) -1)%5 AS Col,
       (ROW_NUMBER() OVER (ORDER BY Column1) -1)/5 AS Row
FROM YourTable       
)
SELECT MAX(CASE WHEN Col = 0 THEN Column1 END),
       MAX(CASE WHEN Col = 0 THEN Column2 END),
       MAX(CASE WHEN Col = 1 THEN Column1 END),
       MAX(CASE WHEN Col = 1 THEN Column2 END),
       MAX(CASE WHEN Col = 2 THEN Column1 END),
       MAX(CASE WHEN Col = 2 THEN Column2 END),
       MAX(CASE WHEN Col = 3 THEN Column1 END),
       MAX(CASE WHEN Col = 3 THEN Column2 END),
       MAX(CASE WHEN Col = 4 THEN Column1 END),
       MAX(CASE WHEN Col = 4 THEN Column2 END)
FROM CTE
GROUP BY Row
ORDER BY Row

SQL Fiddle

Sql-server – Combine – Select Into, with Insert Into Select

When the automation fires off the second time, I get an error because the table already exists and, consequently, the second round of data isn't inserted.

I would suggest you to put a condition to check the existence of the table

If exists (select 1 from sys.tables where name = '' and type = 'U')
begin -- table exists, so insert values in it
insert into table_name values ()
end
else
begin -- table does not exist, so create it
create table table_name (column datatype)
insert into table_name values ()
end

You can even use below sql to create new blank table as the where 1=0 will always be false.

Note that it wont create any indexes from old_table into new_table.

SELECT *
INTO new_table
FROM old_table where 1=0

To capture errors, you can use TRY/CATCH

Best Answer

Related Solutions

Sql-server – Need to divide single column into multiple columns

Sql-server – Combine – Select Into, with Insert Into Select

Related Question