Solved

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

Posted on 2014-10-07
5
109 Views
Last Modified: 2014-10-07
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
Comment
Question by:Delphiwizard
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40365804
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
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 40365833
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 51

Expert Comment

by:Vitor Montalvão
ID: 40365853
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
 
LVL 2

Accepted Solution

by:
Pratik Makwana earned 500 total points
ID: 40365939
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
 

Author Closing Comment

by:Delphiwizard
ID: 40366082
This solution was the only one that worked. Thanks all for sharing.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question