Counting distinct rows in SQL

From: <nathan.cutmore_at_burohappold.com>
Date: 20 Jan 2005 11:07:07 -0800
Message-ID: <1106248027.511529.42060_at_z14g2000cwz.googlegroups.com>

Hello

I am trying to develop an SQL query that turns this table...

---

item_id item_title location_id location

---

476         Abfab          2               Dubai
476         Abfab          6               London
476         Abfab          6               London
476         Abfab          3               Paris


---------------------------------------------------------------

(Note: This table shows that there are 2 copies of this item located at London and 1 each in Dubai and Paris. Item_id and item_title will always be the same. Location_id and location are related to eachother. There are up to 10 different locations so I do not want to hard code the location names.)

Into this...

---

item_id item_title location_id location copies

---

476         Abfab         2                Dubai         1
476         Abfab         6                London        2
476         Abfab         3                Paris         1


---------------------------------------------------------------

I could manipulate the first table using VB or C# in the page's code behind and output the results to a datagrid or dataset etc to view the results as required in the second table. But is there any way of doing this with an SQL query which I can use in a stored procedure without adding to the code-behind. Any ideas welcome? Received on Thu Jan 20 2005 - 20:07:07 CET