January 17, 2007 at 3:15 pm
Can someone please help? My data will be like this:
Product_number Size Description
A111 Sm test1
A111 M test1
A111 L test1
How can I combine the sizes so that the query will return 1 record like below:
Product_Number Size Description
A111 Sm, M, L test1
Thanks for any inputs.
Minh Vu
January 17, 2007 at 5:42 pm
Hi,
It can be done many ways, depends on the solution you want.
Here how it can be done in a simple query;
select
product,
max(case productsize when 'Sm' Then 'Sm,'Else ''End) +
max(case productsize when 'm' Then 'm, ' Else ''End) +
max(case productsize when 'l' Then 'l'Else ''End) as newsize ,
Productdescription
from
producttest
Group by
product,
Productdescription
Order by
product,
Productdescription
This solution has it limitations but works.
Advanced solutions are also possible, but then you will have to go into
writing code in stored procedures, triggers, functions, cursors etc.....
Have fun with it
Cheers
Arthur
January 18, 2007 at 2:06 am
Hi,
main limitation of the above solution is, that:
- you have to modify it any time there is a new value
- the more different values there is in the column, the more complicated it is
If you only have 3 or 5 sizes and know that this is something that "never" changes, you can take a risk and use it - but I wouldn't recommend it, unless it is something that isn't permanent. Remember, you can later leave the company or do another job in it, and someone will have to maintain the code.
This is solution I would recommend:
/*create test data*/
CREATE TABLE product (product_number VARCHAR(10), VARCHAR(5), [description] VARCHAR(30))
INSERT INTO product(product_number, , [description])
SELECT 'A111', 'Sm', 'test1'
UNION ALL
SELECT 'A111', 'M', 'test1'
UNION ALL
SELECT 'A111', 'L', 'test1'
/*function for concatenating - returns comma delimited list of "size" values for a specific product number; based on assumption that product_number identifies a product (i.e. product_number and size could be a PK/unique index of the table)*/
CREATE FUNCTION dbo.concat_sizes (@product VARCHAR(10))
RETURNS VARCHAR(500)
AS
BEGIN
DECLARE @SizeList VARCHAR(500)
SELECT @SizeList = ISNULL(@SizeList+ ', ' + CHAR(13), space(0)) + product.
FROM product
WHERE product_number = @product
ORDER BY /*orders the list alphabetically*/
RETURN @SizeList
END
/*this is how you use the function in queries*/
SELECT product_number,
dbo.concat_sizes(product_number),
[description]
FROM product
GROUP BY product_number, [description]
No matter how many different sizes you add, this will always work without any adjustments (except when the size limit of @SizeList is reached, but there is no reason why you shouldn't make it VARCHAR(2000) or even VARCHAR(8000) right from the start, if you think there is any chance of getting that big).
January 18, 2007 at 8:52 am
Probably you can write a small function and call it. Something like this
CREATE FUNCTION GetSize(
@ProductNo VARCHAR(100),
@Description VARCHAR(100)
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @Size VARCHAR(1000)
SET @Size = ''
SELECT @Size =@Size + CASE WHEN @Size = '' THEN '' ELSE ', ' END + Size
FROM
Products --Replace with your table name
WHERE
Products.Product_Number = @ProductNo
AND
Products.[Description] = @Description
RETURN @Size
END
GO
SELECT DISTINCT Product_number,
dbo.GetSize(Product_number,Description) AS [Size],
[Description]
FROM
Products -- Replace with your tablename and also the columns appropriately.
Prasad Bhogadi
www.inforaise.com
January 18, 2007 at 1:49 pm
Thanks so much for all of the help. It works lovely for me.
Have a great day!
Minh
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply