March 8, 2007 at 7:56 am
I've got this query:
Set nocount on
select inventoryname from inventory
inner JOIN InventoryUsage
on Inventory.inventoryID = InventoryUsage.inventoryID
and Inventory.InventoryVersion = InventoryUsage.InventoryVersion
inner JOIN inventorysale
on inventorysale.InventoryUsageID = InventoryUsage.InventoryUsageID
and InventorySale.InventoryUsageVersion = InventoryUsage.InventoryUsageVersion
where inventorysale.saleid = 67026
is it possible to get all the results on one line with a comma seperating them?
March 8, 2007 at 11:24 am
Set nocount on
declare @results varchar(8000)
select @results = isnull(@results + ',', '') + inventoryname from inventory
inner JOIN InventoryUsage
on Inventory.inventoryID = InventoryUsage.inventoryID
and Inventory.InventoryVersion = InventoryUsage.InventoryVersion
inner JOIN inventorysale
on inventorysale.InventoryUsageID = InventoryUsage.InventoryUsageID
and InventorySale.InventoryUsageVersion = InventoryUsage.InventoryUsageVersion
select @results
Russel Loski, MCSE Business Intelligence, Data Platform
March 9, 2007 at 1:07 am
In case you need several rows to be returned (example : list of products with column "colour", showing all available colours for each respective product in form 'red, white, blue'), you will need to write a function based on the same idea as RLoski posted. This function will return comma delimited list for each row - but be careful where you use it and test it first, on large tables it can be slow.
Example (drop statements at the beginning not included on purpose - if you already have table/function with this name, you may want to check first what it is before dropping it):
---test table definition
CREATE TABLE products(productid int, categoryid int, productname varchar(10))
INSERT INTO products (productid, categoryid, productname) VALUES (101, 10, 'Product 1')
INSERT INTO products (productid, categoryid, productname) VALUES (102, 10, 'Product 2')
INSERT INTO products (productid, categoryid, productname) VALUES (103, 12, 'Product 3')
INSERT INTO products (productid, categoryid, productname) VALUES (104, 12, 'Product 4')
INSERT INTO products (productid, categoryid, productname) VALUES (105, 12, 'Product 5')
GO
--function to produce comma delimited list
CREATE FUNCTION dbo.GetProdList (@Id INT)
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @List VARCHAR(1000)
SELECT @List = ISNULL(@List+ ', ', '') + p.productname
FROM Products p
WHERE p.categoryid = @id
RETURN @List
END
GO
--this is how to use the function
SELECT p.categoryid, dbo.GetProdList(p.categoryid) as ProductList, count(*) as No_of_Products
FROM Products p
GROUP BY p.categoryid
--cleanup
DROP FUNCTION dbo.GetProdList
DROP TABLE products
March 9, 2007 at 5:29 am
works excellently,
Thanks guys
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply