October 24, 2006 at 3:27 pm
Hi,
I have a products table. I want to select all the products under each category in a comma seperated string.
Products
{productid, categoryid, productname}
101, 10, Product 1
102, 10, Product 2
103, 12, Product 3
104, 12, Product 4
105, 12, Product 5
I want the select query to return this.
{categoryid, products}
10 Product 1, Product 2
12 Product 3, Product 4, Product 5
Can we do this in a single query?
Thanks.
October 24, 2006 at 4:17 pm
I don't have an excat answer. I know this works for a particular category.
DECLARE @ProductName VARCHAR(1000)
set @ProductName =''
SELECT @ProductName =@ProductName + ', ' +
ProductName
from Products where CategoryID = 20
select @ProductName
October 24, 2006 at 4:31 pm
I would like to do it in a single query, if its possible.
October 24, 2006 at 4:35 pm
I have another question regarding the same example. In addition to comma seperated products, I would like to select the number of products under each category, just the number. For the above example, it will be
{categoryid, products}
10 2
12 3
THanks.
October 24, 2006 at 4:40 pm
Hope this helps
DECLARE @tbl TABLE
(RowId VARCHAR(20),
RValue VARCHAR(30),
Lineage VARCHAR(3000) DEFAULT '',
Depth INT)
insert into @tbl(RowId,RValue) values('WV', 'Kanawha')
insert into @tbl(RowId,RValue) values('WV', 'Putnam')
insert into @tbl(RowId,RValue) values('WV', 'Dodridge')
insert into @tbl(RowId,RValue) values('WV', 'Rhone')
insert into @tbl(RowId,RValue) values('OH', 'Buckeye')
insert into @tbl(RowId,RValue) values('OH', 'Kings Island')
insert into @tbl(RowId,RValue) values('OH', 'Hamilton')
DECLARE @Sql VARCHAR(3000)
DECLARE @RowId VARCHAR(20)
DECLARE @val VARCHAR(3000)
DECLARE @Cnt INT
SELECT @val = ''
UPDATE Source
SET @RowId = CASE WHEN @RowId IS NULL THEN Source.RowId ELSE @RowId END,
@Cnt = CASE WHEN @Cnt IS NULL THEN 0 ELSE @Cnt END,
@Val = Lineage = CASE WHEN @RowId = Source.RowId THEN @val+ISNULL(Source.RValue,'NULL')+',' ELSE ISNULL(Source.RValue,'NULL')+',' END,
@Cnt = Depth = CASE WHEN @RowId = Source.RowId THEN @Cnt+1 ELSE NULL END,
@RowId = Source.RowId
FROM @tbl AS Source
SELECT RowId,
SUBSTRING(MAX(Lineage),1,LEN(MAX(Lineage))-1) AS Lineage,
COUNT(1) AS NoOfCounts
FROM @tbl
GROUP BY RowId
Ram
October 25, 2006 at 12:48 am
This kind of vertical to horizontal rotation cannot be done with pure sql. You will need to resort to a function. Horizontal to vertical rotation can be done using case statements and because you know the number of columns ahead of time.
October 25, 2006 at 3:51 am
As Michael said, you will need a function to create the concatenated list. Then your query would look like this:
SELECT p.categoryid, dbo.GetProdList(p.categoryid) as ProductList, count(*) as No_of_Products
FROM Products p
And this is the function used in this query:
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply