How to merge multiple coulmns into single column

Question

0.00/5 (No votes)

See more:

Hi Friends

Can any one suggest me , I need to merge the multiple columns of a repeated rows into a single column of a single with SEMICOLON using sql query .

For EXAMPLE:
The table contains the column and their value is ,

SerialNumber       ProductNumber         ProductNumber
   QC12345             BA98765             1
   QC67890             BA98765             3
   QC00000             BA98765             1
   QC00011             BA12345             2
   QA01234             BA12345             2
   .                       .                       .
   .                       .                       .
   .                       .                       .
   .                       .                       .

The ABOVE VALUE IS JUST EXAMPLE , VALUES IS UNKNOWN , IT MIGHT BE MORE THAN 100 OR 200 OR EVEN 1000 RECORDS MAY BE AVAILABLE BUT

the OUTPUT FORMAT which I want is :

     SerialNumber             ProductNumber       Quantity
QC12345;QC67890;QC00000          BA98765              5
  QC00011;QA01234                BA12345              4

So can any one provide me a Query to create output as like the above format.

Posted 14-Oct-11 21:01pm

SARAVANAKUMAR.M

Updated 14-Oct-11 21:39pm

Mehdi Gholam

v3

Add a Solution

2 solutions

Solution 1

This is a bad idea.

You are better off using two separate queries one for the count on product numbers and the other for product number serial numbers.

Posted 14-Oct-11 21:45pm

Mehdi Gholam

Comments

RaviRanjanKr 16-Oct-11 3:09am

Appreciating Idea, MY 5+

Mehdi Gholam 16-Oct-11 3:14am

Thanks, Ravi.

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)

André Kraak · Accepted Answer · 2011-10-14T21:46:00

Based on the answer[^] to a similar question[^] I think the query should look something like this:

SQL

SELECT REPLACE(RTRIM((SELECT SerialNumber + ' ' FROM myTable
                      WHERE (ProductNumber = T.ProductNumber)
                      FOR XML PATH (''))),' ',';'),
       ProductNumber, SUM(ProductQuanity) AS Quantity
FROM myTable T
GROUP BY ProductNumber