SQLTeam.com | Weblogs | Forums

Group By? Array?

I have 3 related tables: Claims, Names, Judicial
Each claim can have several names, but only one Judicial info.

I've created a view where I can list just the fields I want from each table, but it shows a row for each name, repeating for each one the Claim ID and all the Judicial Info. I would like it to be grouped (?) and show an array(?) of the names, separated by a comma.

What it shows now:

ClaimID          Names         Court Name          City           Fee
-------------------------------------------------------------------------------
100001            Joe          Court Name         Chicago         100
100001            Tom          Court Name         Chicago         100
100001            Lisa         Court Name         Chicago         100

What I would like:
From ClaimID (there is only one field) | Names (all on the same row separated by a comma) | From Judicial (Court name, City, Court Fee)

 ClaimID          Names             Court Name        City         Fee
 ----------------------------------------------------------------------------
 100001      Joe, Tom, Lisa         Court Name       Chicago       100

Can I achieve this through grouping by multiple columns? And how can I make the names array(?)?
Thanks in advance.

So I solved it making one query for the Names Table and joining that view with the rest of the fields from the other tables in another query.

Based on:

SELECT a.articleId, title, STRING_AGG (tag, ',') as tags
FROM dbo.Article AS a
LEFT JOIN dbo.ArticleTag AS t
    ON a.ArticleId = t.ArticleId

GROUP BY a.articleId, title;

Thank you, I'll also test that :slight_smile:

hi

i tried to do this .. hope it helps :slight_smile: :slight_smile:

drop create data ....
drop table #data 
go 

create table #data 
(
ClaimID int, 
Names  varchar(100), 
CourtName varchar(100),
City varchar(100), 
Fee int
)
go 

insert into #data select 100001,'Joe','Court Name ','Chicago', 100
insert into #data select 100001,'Tom','Court Name','Chicago',  100
insert into #data select 100001,'Lisa','Court Name','Chicago ', 100

select * from #data 
go
SQL ..
SELECT top_query.claimid, 
       top_query.courtname, 
       top_query.city, 
       top_query.fee, 
       (SELECT Stuff([list], 1, 1, '') AS stuff_list 
        FROM   (SELECT ',' + Cast(names AS VARCHAR(255)) AS [text()] 
                FROM   #data sub 
                WHERE  sub.claimid = top_query.claimid 
                FOR xml path('')) sub_query([list])) AS names
FROM   #data top_query 
GROUP  BY claimid, 
          courtname, 
          city, 
          fee

1 Like