SQL Server Performance Forum – Threads Archive

Faster way to do this

Hi, I’m new to the forum, but not new to this great site. I’m trying to find the fastest way to do the following : I have a table called Equipment_Properties that holds all the properties from a given equipment. The fields are Equipment_ID and Property_Value. I’m trying to return all properties for any equipment on one row so that I can concatenate the values in one field separated by ‘|’. There can be any number of properties for each equipment. So the followind data :
Equipment_ID-Property_Value
1 -100 HP
1 -10V
1 -200 RPM
2 -20A
2 -34" Should return :
Equipment_ID-Result_Wanted
1 -100 HP|10V|200 RPM
2 -20A|34"
The only way I’ve been able to do this is by using a function and a cursor, but it’s extremely slow. This data is needed everywhere in my app, so any help would be greatly appreciated. Thanks, Max

---

Can you post the code you used?
Madhivanan Failing to plan is Planning to fail

---

Hi Max,
I would create a function to concatenate the properties and select that value…something like this: CREATE FUNCTION [dbo].[CommaDelimitedEquipmentProperty] (@EquipmentId VARCHAR(20))
RETURNS VARCHAR(4000) AS
BEGIN DECLARE @ResultList VARCHAR(4000) SELECT @ResultList = (COALESCE(@ResultList + ‘| ‘, ”) + Property_Value )
FROM Equipment_Properties
WHERE EquipmentId = @EquipmentId RETURN @ResultList END and then:
SELECT EquipmentId, dbo.CommaDelimitedEquipmentProperty(EquipmentId)
FROM Equipment_Properties should give you the answer Cant remember where i got the Coalesce code from, but i am pretty sure it came from this website, so thanks to whoever wrote it originally!

---

I have take PUbs database as example query please check if it would serve your purpose :
declare @Names varchar(2000)
declare @st varchar(2000)
select @names=”
select @st=”
select @[email protected]+’|’+ord_num from dbo.sales where stor_id=7067
select distinct @st= stor_id from dbo.sales where stor_id=7067
select @st as Store_ID, @names as Order_num
It will return out put as follow :
Store_ID Order_num
—————————————————————————————————
7067 |D4482|P2121|P2121|P2121
Conclusion : their are four order number for store id 7067
Regards. hsGoswami
[email protected]

---

This might be interesting:http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

---

Thanks for the infor, I will try that. Madhivanan, here’s what what my code looks like : CREATE FUNCTION
[dbo].[Properties] (@EQUIPMENTID AS INT)
RETURNS VARCHAR(4000) AS BEGIN DECLARE @RESULTS AS VARCHAR(400) DECLARE @VALUE AS VARCHAR(60) DECLARE Cur CURSOR FAST_FORWARD FOR
SELECT Property_Value FROM Property_Values
WHERE [email protected]
ORDER BY Property_Value OPEN Cur SET @RESULTS=” FETCH NEXT FROM Cur INTO @VALUE WHILE @@FETCH_STATUS = 0 BEGIN SET @RESULTS = @RESULTS + ‘ / ‘ + @VALUE FETCH NEXT FROM Cur INTO @VALUE END IF @RESULTS<>” BEGIN SET @RESULTS = RIGHT(@RESULTS,LEN(@RESULTS) – 1) END CLOSE Cur DEALLOCATE Cur RETURN @RESULTS END

---

You really don’t need a cursor for this. Avoid it! —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

---

Yes
Follow the link prvided by Frank
Madhivanan Failing to plan is Planning to fail

---

It looks like the fastest solution is the scalar UDF from this page :
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true Not quite the performance improvement I was hoping for, but still faster. Thanks all for your help. Max

---

]]>