combine multiple columns into single columns in sql server

Question

4.00/5 (1 vote)

See more:

SQL-Server-2008R2

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

below is my table data :

SQL

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 :

SQL

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

any help would be appreciated.

thanks in advance.

Posted 17-Jan-16 22:41pm

ketan chandpa

Updated 18-Jan-16 1:08am

v2

Add a Solution

Comments

Herman<T>.Instance 18-Jan-16 4:50am

UNPIVOT

ketan chandpa 18-Jan-16 4:52am

thanks for your comment but it would be great if you provide any example so i understand the concept.

Herman<T>.Instance 18-Jan-16 5:00am

see here

ketan chandpa 18-Jan-16 5:06am

i use unpivot but it gives me one columns with 18 rows.
the results also included the empty data from the table and i dont want that.
so any other help or example you have?.

ketan chandpa 18-Jan-16 5:27am

Thanks for comments but i solve this issue in another way using union and i got the result what i want but there is only one problem that is the Serial number of the rows are repeat like 1,2,3,2,3,2,3,6,6 and i want this in serial number like 1,2,3,4,5,6 ....
any idea you have?

Tomas Takac 18-Jan-16 8:13am

I'd say UNION is the way to go. As for the sequence number it depends if there is a id or key in your original table.

2 solutions

Solution 1

As you found out in the comments, UNION is much easier than UNPIVOT

For example:

SQL

SELECT SrNo, Field1
FROM table1
UNION 
SELECT SrNo, Field2
FROM table1
...

UNION ALL will give you duplicates and UNION will give you unique SrNo, field combinations.

Posted 18-Jan-16 2:15am

ZurdoDev

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tomas Takac · Accepted Answer · 2016-01-18T03:10:00

You need to select the company1 .. company3 in three separate queries and concatenate those using UNION. then you need to generate a new sequence number using your existing sequence number. To keep the rows in order so company1 records come first and company3 records come last you need to add one more identifier for precedence. Then you feed both to ROW_NUMBER() and you are done.

SQL

WITH cte AS
(
    SELECT 1 AS SrNrGroup, SrNr, Company1 AS Company FROM yourtable WHERE Company1 IS NOT NULL
    UNION ALL
    SELECT 2 AS SrNrGroup, SrNr, Company2 FROM yourtable WHERE Company2 IS NOT NULL
    UNION ALL
    SELECT 3 AS SrNrGroup, SrNr, Company3 FROM yourtable WHERE Company3 IS NOT NULL
)
SELECT ROW_NUMBER() OVER(ORDER BY SrNrGroup, SrNr) AS SrNr, Company FROM cte