Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
dulaney84
Starting Member
3 Posts |
Posted - 2009-01-28 : 12:27:35
|
I have a select query that I want to group by size.Here is what I am working with:SELECT Items.SKU, Items.Price, Items.Description, ProdItemMap.PID AS DeptID, DepartmentList.Name AS DeptName, SKUSizes.Description AS SizeFROM Items INNER JOIN ProdItemMap ON Items.SKU = ProdItemMap.SKU INNER JOIN DepartmentList ON ProdItemMap.PID = DepartmentList.Catagory INNER JOIN SKUSizes ON Items.SKU = SKUSizes.SKU CROSS JOIN InventoryWHERE (Inventory.ItemCode = Items.SKU + SKUSizes.SKUQual) AND (Inventory.OnHandQty > 0)ORDER BY Items.SKUThis will return all my items in the database that are in stock. Here is a sample of what it will return:SKU Price Description DeptID DeptName Size"111" "5.00" "shirt" "1" "tshirt" "Small""111" "5.00" "shirt" "1" "tshirt" "Medium""111" "5.00" "shirt" "1" "tshirt" "Large""112" "8.00" "shirt" "1" "tshirt" "Small""112" "8.00" "shirt" "1" "tshirt" "Medium"...What I would like to return is group all the sizes in one field separated by a comma. For example:SKU Price Description DeptID DeptName Size"111" "5.00" "shirt" "1" "tshirt" "Small, Medium, Large""112" "5.00" "shirt" "1" "tshirt" "Small, Medium"...How can I update my select statement to accomplish this?Thanks |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-28 : 12:35:31
|
[code]SELECT Items.SKU, Items.Price, Items.Description, ProdItemMap.PID AS DeptID, DepartmentList.Name AS DeptName, LEFT(s.t,LEN(s.t)-1) AS SizeFROM Items INNER JOINProdItemMap ON Items.SKU = ProdItemMap.SKU INNER JOINDepartmentList ON ProdItemMap.PID = DepartmentList.CatagoryCROSS APPLY(SELECT Description + ','FROM SKUSizes WHERE SKU =Items.SKUFOR XML PATH(''))s(t) CROSS JOINInventoryWHERE (Inventory.ItemCode = Items.SKU + SKUSizes.SKUQual) AND (Inventory.OnHandQty > 0)ORDER BY Items.SKU[/code] |
|
|
dulaney84
Starting Member
3 Posts |
Posted - 2009-01-28 : 12:56:08
|
PERFECT!! Thank you.I had to add a Distinct at the top and include the inner join to the SKUSizes since I was using the SKUSizes.SKUQuals in the WHERE clause. But it works perfectly now.Thank you. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-28 : 12:58:47
|
welcome |
|
|
Nageswar9
Aged Yak Warrior
600 Posts |
Posted - 2009-01-28 : 23:42:16
|
Try this also,declare @temp table (SKU int, Price varchar(32), Description varchar(32),DeptID int, DeptName varchar(32),Size varchar(32))insert into @tempselect 111,5.00,'shirt',1,'tshirt','Small' union allselect 111,5.00,'shirt',1,'tshirt','Medium' union allselect 111,5.00,'shirt',1,'tshirt','Large' union allselect 112,8.00,'shirt',1,'tshirt','Small' union allselect 112,8.00,'shirt',1,'tshirt','Medium'select distinct sku,price,description,deptid,deptname,stuff(( select distinct ','+size from @temp where sku = t.sku for xml path('')),1,1,'') as size from @temp t |
|
|
|
|
|
|
|