Column to Row - concatente

[chamilz]

Hi,
I really appreciate your help to find a solution for this.

I have a product_table which has product details as below..

Pro_id attribute size_code
1 A 900
1 A 910
1 A 920
2 B 900
2 B 910
2 B 920
3 A 910
3 A 920

Attribute is always defined base on the product. Here i want to organise size_code in the following format...
e.g.
product_id Attribute SIZE RANGE
1 A 900910920
2 B 900910920
3 A 910920

I found concatenation function in this forum, but it takes more than 30 mints to run for 70K records.
This is the function I used,

ALTER function dbo.udf_concat_nrf_code
(
@id int
)
returns varchar(1000) as
begin

declare @concat varchar(1000)
select @concat= coalesce(@concat,'')+ cast(size_code as varchar(3))
from product
where product_id = @id
return @concat
end -- end function
----------------------------

select distinct product_id,attribute,
'size_range' = dbo.udf_concat_nrf_code (product_id)
from product
group by product_id, attrubute

Do you have work around to find this solution?

Thanks in advance.

 

[JamesLean]

Does this work any better?

SELECT product_id,
  attribute,
  dbo.udf_concat_nrf_code(product_id)
FROM (
    SELECT DISTINCT product_id, attribute
    FROM product
  ) t

--James

 

[chamilz]

Hi James…
Thanks you prompt response…it took me approx. 12 mints to run this. Much better than my query..
Thanks.