Trying to combine multiple rows of result set into single row

Question

select C.customerId,(C.lastName+', '+C.firstName) as CustomerName, C.companyName, 
D.companyName+' ('+D.lastName+','+D.firstName+')' 
as "Parent CompanyName(Last, First)",S.siteId, S.nickName as siteName, 
dbo.GetSiteTelemetryBoxList(s.siteId) as "DeviceId's", 
dbo.GetSiteTelemetryBoxSKUList(S.siteId,0) as SKU
from Site S
INNER JOIN Customer C ON S.customerId = C.customerId 
INNER JOIN Customer D ON D.customerId = C.parentCustomerId
where S.createDate between DATEADD(DAY, -65, GETUTCDATE()) and GETUTCDATE()
order by C.customerId, S.siteId

The above query returns values that look like this:

CID     CustomerName      companyName           Parent CompanyName(Last, First)                 SiteName                    DeviceId        SKU
888296  DeYoung, Scott    DeYoung Farms         Mercier Valley Irrigation (Mercier,Ralph)       H E east                    200241        NETB12WR
890980  Rust, Marcus      NULL                  Chester Inc. (Young,Scott)                      Byroad east                 346370        NETB12WR
890980  Rust, Marcus      NULL                  Chester Inc. (Young,Scott)                      Byroad west                 345431        NETB12WR
891094  Pirani, Mark      A Pirani Farm         AMX Irrigation (Burroughs,Michael)              hwy 64 south                333721        UNKNOWN
891094  Pirani, Mark      A Pirani Farm         AMX Irrigation (Burroughs,Michael)              HWY 64 North                250162        NETB12WR
891094  Pirani, Mark      A Pirani Farm         AMX Irrigation (Burroughs,Michael)              HWY 64 West                 250164        NETB12WR
891094  Pirani, Mark      A Pirani Farm         AMX Irrigation (Burroughs,Michael)              HWY 64 East                 250157        NETB12WR
891430  Gammil, Bob       Gammil FArms          AMX Irrigation (Burroughs,Michael)              angel                       333677        UNKNOWN
891430  Gammil, Bob       Gammil FArms          AMX Irrigation (Burroughs,Michael)              cemetery                    333564        UNKNOWN

The problem I face now is that if a customerId/Name is repeating in the result set. The SiteName, deviceId, SKU should be concatenated to represent the data as one value. For example, Mark Pirani row would look like

 CID   CustomerName  ...   SiteName                                               DeviceId's                   ...
891904 Pirani, Mark  ...   hwy 64 south, HWY 64 North, HWY 64 West, HWY 64 East   333721,250162,250164,250157  ...

Easier depends on your expertise. Better depends on your expertise and the expertise of anyone else who will maintain it. What object is your data in when you get it back from SQLServer? — Kevin, Jul 11 '13 at 20:04
@PramodRoy You should avoid asking things like "Is it better/easier to format it using C#". This sort of question will result in opinion based answers, and isn't really a good fit for Stack Overflow. That part of your question would be better asked on codereview.stackexchange.com. — Patrick Sebastien, Jul 11 '13 at 20:18

score 0 · Answer 1 · answered Jul 11 '13 at 20:04

You can convert the rows with something like this to transform the rows into a concatenated string:

 select
   distinct  
stuff((
    select ',' + u.username
    from users u
    where u.username = username
    order by u.username
    for xml path('')
),1,1,'') as userlist
from users
group by username

score 0 · Answer 2 · edited May 23 '17 at 11:56

0

I believe this is more of a SQL query issue than a C# code issue, or more appropriately I believe it more efficient to solve this problem at the query level rather than the code level. Off the top of my head you can use SELECT DISTINCT or GROUP BY clauses.

Here is another StackOverflow question addressing this issue - How do I (or can I) SELECT DISTINCT on multiple columns?

edited May 23 '17 at 11:56

Community

1
1

answered Jul 11 '13 at 20:05

Karl Anderson

34,606
12
65
80

The problem for me is not in getting unique values but to concatenate the values of two different rows if the same Id is encountered. – 1Mojojojo1 Jul 11 '13 at 20:11

score -1 · Accepted Answer · edited May 23 '17 at 12:05

I did some digging and found a few ways to implement it. Basically, the simple solution for this is using mysql's group_concat function. These links discuss how the group_concat can be implemented for SQL server. You can choose one based on your requirements.

Simulating group_concat MySQL function in Microsoft SQL Server 2005? -- This thread discusses a few ways to implement it.
Flatten association table to multi-value column? -- This thread discusses the CLR implemenation of it.
http://groupconcat.codeplex.com/ -- This was just perfect for me. Exactly what I was looking for. The project basically creates four aggregate functions that collectively offer similar functionality to the MySQL GROUP_CONCAT function.

Trying to combine multiple rows of result set into single row

3 Answers3