Get unique row or merge the values on unique rows

Asked Feb 24 '14 at 20:36

Active Feb 24 '14 at 20:52

Viewed 39 times

SQLServer2008R2 and ASP.NET(VB) - VS2012

I am tring to use many-to-many relationship. I have a table to enter users, another one for the incidents and created a join table with the primary key from both the tables. I am trying to display top 20 incidents from the incident table and get the patron id from join table and then get the name of the patron from patron table. For an incident with two patrons, it creates two rows. I tried to use GROUP by but the query did not like it. Is there a way that I can display only one incident id with both patron names listed?

SELECT TOP (20) 
  tblIncident.Inci_ID
 ,tblIncident.Library
 ,tblIncident.Inci_date
 ,tblIncident.Inci_time
 ,tblIncident.Created_By
 ,tblJoin.PatronID AS Patron_ID
 ,tblPatron.FName + ' ' + tblPatron.LName AS FullName
FROM tblIncident
 INNER JOIN tblJoin ON tblIncident.Inci_ID = tblJoin.InciID
 INNER JOIN tblPatron ON tblJoin.PatronID = tblPatron.PatronID
WHERE tblIncident.Active = 'True'
ORDER BY tblIncident.Inci_date DESC

edited Feb 24 '14 at 20:52

NickyvV

1,720
2
16
18

asked Feb 24 '14 at 20:36

Nita

How would you have both the names listed then? Like comma-separated in one column? – NickyvV Feb 24 '14 at 20:51
@NickyvV - as comma separated. :) – Nita Feb 24 '14 at 20:53
Stuff and for xml maybe...http://stackoverflow.com/questions/21635423/select-statement-that-combines-rows-in-a-subquery-pivot/21636008#21636008 – Guy Nethery Feb 25 '14 at 00:16

Get unique row or merge the values on unique rows

0 Answers0