Web Development

General discussion

Locked

SQL Merge Results of column in Query fld

By info ·
Platform: SQL 2000 on Win2k Server
Would like to use a stored procedure or Query that will take the results of a Column from a query(Select PartName FROM tblparts WHERE WOID=20)with a matched ID from the parent query (Select WOID, WOName, PartsList= ? FROM tblWorkorders INNER JOIN ...), and use the merged results as a column for another query. I believe the PartsList = thing will not work.

tblparts
ID |WOID |partname
1 |20 |RJ45mod tip
2 |20 |Cat5e Cable Per Feet

tblWorkOrders
WOID |WOName
22 |Test Work Order

Would like to have a results set that would have
WOID |WOName |PartsUsed(as a text field)
22 |Test Work Order |RJ45mod tip, Cat5e Cable Per Feet

I could do this via VBA in code but would like it for speed reasons done via a stored procedure or a query.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

SQL Merge Results of column in Query fld

by MadestroITSolutions In reply to SQL Merge Results of colu ...

Here is a procedure that will take the work order ID as a parameter, and return all the Parts concatenated as one string:

-------------------------------------------

CREATE PROCEDURE GetPartsAsText
@WorkOrderID numeric

AS

begin
declare @PartName varchar(50)
declare @AllParts varchar(1000)
declare @TempParts varchar(1000)

DECLARE ctblParts CURSOR FOR
SELECT PartName FROM tblParts WHERE WOID = @WorkOrderID

OPEN ctblParts

FETCH NEXT FROM ctblParts INTO @PartName

WHILE @@FETCH_STATUS = 0

BEGIN
PRINT @AllParts
SELECT @AllParts = RTRIM(ISNULL(@AllParts,'')) + RTRIM(@PartName) + ','
FETCH NEXT FROM ctblParts INTO @PartName
END

CLOSE ctblParts
DEALLOCATE ctblParts

SELECT SUBSTRING(@AllParts,1,LEN(@AllParts)-1)

end
GO

------------------------------------------

It works, I tested it myself.
You can either call this procedure for every record, or modify it to create a temp table and return the complete recorset with the new column added. Sadly, I was going to do it, but run out of time, he he.

Good luck!

Collapse -

SQL Merge Results of column in Query fld

by info In reply to SQL Merge Results of colu ...

Thanks, adding 100 extra points

Collapse -

SQL Merge Results of column in Query fld

by info In reply to SQL Merge Results of colu ...

This worked great. I was banging my head with this, new it could be done, but not experienced enough with stored procedures. Adding extra points for a great reply

Cheers

Collapse -

SQL Merge Results of column in Query fld

by info In reply to SQL Merge Results of colu ...

This question was closed by the author

Related Discussions

Related Forums