Solved

# GROUPBY where a nVarChar-field from each row is within the group concatenated in one field

Posted on 2014-10-07
96 Views
Hi,

Suppose I have following data in a table.
``````NameID Product TypeID     Amount
1      ProdA   1          100
1      ProdB   1          150
2      ProdC   1           50
3      ProdA   1          100
3      ProdC   1           50
4      ProdB   1          150
``````
The result I need to get is:
``````NameID Product       TypeID     Amount
1      ProdA ProdB   1          250
2      ProdC         1           50
3      ProdA ProdC   1          150
4      ProdB         1          150
``````
So all distinct rows are grouped, regardless of the product, but all products per NameID will be combined into one field.
There might be more than two products per NameID.
How can this be done?
0
Question by:Delphiwizard

LVL 24

Expert Comment

``````select NameID,
ltrim((select ' ' + Product
from myTable B
where A.NameID = B.NameID
for xml path(''), elements)) as Product, TypeID, sum(Amount) as Amount
from myTable A
group by NameID, TypeID
``````
0

LVL 45

Expert Comment

Hi Wizard,

There are several ways to do this, though none are truly trivial.  A function or stored procedure can be used to loop on the similar data to generate the concatenated name string.  You can also use recursive SQL to generate the concatenated name.  I'll be glad to walk you through the recursive SQL if you'd like.

Here's an article that describes the process.

http://www.experts-exchange.com/Database/DB2/A_3618-Recursive-SQL-in-DB2-Converting-rows-to-columns.html

Kent
0

LVL 45

Expert Comment

You can use STUFF function to do the trick.
``````SELECT P1.NameID, STUFF((SELECT ' '+P2.Product FROM YourTableNameHere P2 WHERE P2.NameID=P1.NameID FOR XML PATH('')) , 1 , 1 , ''), P1.TypeID, SUM(P1.Amount)
FROM YourTableNameHere P1
GROUP BY P1.NameID, P1.TypeID
``````
0

LVL 2

Accepted Solution

Pratik Makwana earned 500 total points

select nameid, product, typeid,Amount
from (
select nameid,
STUFF ((Select ' '+Product From ProductDetail p1 Where p2.NameID=P1.NameID
For XML PATH('')),1,1,'') as Product, typeid
, row_number() over (partition by nameid order by nameid) r
, sum(amount) over (partition by nameid) as Amount
from productdetail p2
) firstRow
where firstRow.r = 1
0

Author Closing Comment

This solution was the only one that worked. Thanks all for sharing.
0

## Featured Post

When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…