如何将多个库仑合并为单个列 [英] How to merge multiple coulmns into single column

问题描述

Hi Friends

谁能建议我，我需要使用sql查询将重复行的多列合并为SEMICOLON的单个列.


例如:
该表包含该列，其值为，

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
   .                       .                       .
   .                       .                       .
   .                       .                       .
   .                       .                       .

上面的值只是一个例子，值是未知的，它可能超过100或200，甚至可能有1000个记录

我想要的输出格式是:

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.

推荐答案

这是一个坏主意.

最好使用两个单独的查询，一个查询产品编号，另一个查询产品编号序列.

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.

基于问题 [ ^ ]我认为查询应如下所示:

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

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

这篇关于如何将多个库仑合并为单个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！