How to handle One-to-Many Join without repeat rows

In MS SQL Server I have two tables [Products] and [Product_img] with relationship (ONE TO MANY).

SELECT p_id, name FROM dbo.products

| 15 | | name1 |

| 17 | | name2 |

SELECT p_id, path FROM dbo.product_img

| 1.jpg | | 15 |

| 2.jpg | | 15 |

| 3.jpg| | 17 |

| 4.jpg | | 17 |

SELECT dbo.product_img.p_id, dbo.products.name, dbo.product_img.path FROM dbo.product_img INNER JOIN dbo.products ON dbo.product_img.p_id = dbo.products.p_id

| 15 | | name1 | | 1.jpg |

| 15 | | name1 | | 2.jpg |

| 17 | | name2 | | 3.jpg |

| 17 | | name2 | | 4.jpg |

What is the SQL statement that can show the tables like this (with out 开发者_如何学Crepeating)?

| 15 | | name1 | | 1.jpg | | 2.jpg |

| 17 | | name2 | | 3.jpg | | 4.jpg |

USE MAX AND MIN

SELECT
  dbo.product_img.p_id
, dbo.products.name
, MAX(dbo.product_img.path)
, MIN(dbo.product_img.path) 
FROM dbo.product_img 
INNER JOIN dbo.products 
    ON dbo.product_img.p_id = dbo.products.p_id
 GROUP BY dbo.product_img.p_id , dbo.products.name

If you can have more than two images per product you can do this to get one column with the img path's.

select
  p.p_id,
  p.name,
  stuff((select ', '+i.path
         from product_img as i 
         where p.p_id = i.p_id
         for xml path(''), type).value('text()[1]', 'nvarchar(max)'), 1, 2, '') as path  
from products as p

Result

p_id    name    path
-----   -----   ------------
15      name1   1.jpg, 2.jpg
17      name2   3.jpg, 4.jpg