Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 116
  • Last Modified:

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

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

Open in new window

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

Open in new window

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
Stef Merlijn
Asked:
Stef Merlijn
1 Solution
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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

Open in new window

0
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

Open in new window

0
 
Pratik MakwanaData AnalystCommented:
Here is your solution.....

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
 
Stef MerlijnDeveloperAuthor Commented:
This solution was the only one that worked. Thanks all for sharing.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now