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.

4 total posts (Page 1 of 1)  
| 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

Back to Web Development Forum
4 total posts (Page 1 of 1)  

Related Discussions

Related Forums