PAGANED
asked on
Looping SQL Table with Concatenation
There is a SQL table that has two fields in it.
ProjectID, ProblemControlID
.
I am trying to loop through the table, and while the ProjectID is the same, I want to concatenate the ProblemControlIDs.
.
For Example:
TABLE
ProjectID 1 ProblemControlID5
ProjectID 1 ProblemControlID10
ProjectID 1 ProblemControlID15
ProjectID 2 ProblemControlID7
ProjectID 2 ProblemControlID10
ProjectID 2 ProblemControlID18
OUTPUT
ProjectID 1, ProblemString 5,10,15
ProjectID 2, ProblemString 7,10,18
.
The code that I currently have is not correct.
ProjectID, ProblemControlID
.
I am trying to loop through the table, and while the ProjectID is the same, I want to concatenate the ProblemControlIDs.
.
For Example:
TABLE
ProjectID 1 ProblemControlID5
ProjectID 1 ProblemControlID10
ProjectID 1 ProblemControlID15
ProjectID 2 ProblemControlID7
ProjectID 2 ProblemControlID10
ProjectID 2 ProblemControlID18
OUTPUT
ProjectID 1, ProblemString 5,10,15
ProjectID 2, ProblemString 7,10,18
.
The code that I currently have is not correct.
ALTER PROCEDURE procConcatenateProblemType
AS
BEGIN
SET NOCOUNT ON;
SELECT ProjectID, ProblemControlID FROM dbo.tblProblemControlTEMP ORDER BY ProjectID
DECLARE @ProjectID int
DECLARE @ProjectID2 int
DECLARE @ProblemString nvarchar(500)
CREATE TABLE #tblProblemString(ProjectID int, ProblemString nvarchar(500))
SELECT @ProjectID = ProjectID FROM dbo.tblProblemControlTEMP
WHILE @@ROWCOUNT <> 0
BEGIN
WHILE @ProjectID = (SELECT ProjectID FROM dbo.tblProblemControlTEMP)
BEGIN
SELECT @ProblemString = @ProblemString + ',' + (SELECT ProblemControlID FROM tblProblemControlTEMP)
END
INSERT INTO #tblProblemString VALUES (@ProjectID, @ProblemString)
FETCH NEXT FROM tblProblemControlTemp
END
SELECT * FROM #tblProblemString
END
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I implemented your changes with no change to the output.
The code I have with the While @@RowCount <> 0 and the FETCH seems correct but does not combine the ProblemControlID in the output.
.
I'm trying to get an output table with one row for each projectid.
.
For Example:
TABLE
ProjectID 1 ProblemControlID5
ProjectID 1 ProblemControlID10
ProjectID 1 ProblemControlID15
ProjectID 2 ProblemControlID7
ProjectID 2 ProblemControlID10
ProjectID 2 ProblemControlID18
OUTPUT
ProjectID 1, ProblemString 5,10,15
ProjectID 2, ProblemString 7,10,18
The code I have with the While @@RowCount <> 0 and the FETCH seems correct but does not combine the ProblemControlID in the output.
.
I'm trying to get an output table with one row for each projectid.
.
For Example:
TABLE
ProjectID 1 ProblemControlID5
ProjectID 1 ProblemControlID10
ProjectID 1 ProblemControlID15
ProjectID 2 ProblemControlID7
ProjectID 2 ProblemControlID10
ProjectID 2 ProblemControlID18
OUTPUT
ProjectID 1, ProblemString 5,10,15
ProjectID 2, ProblemString 7,10,18
ASKER
Here the streamlined code:
ALTER PROCEDURE procConcatenateProblemType
AS
BEGIN
SET NOCOUNT ON;
SELECT ProjectID, ProblemControlID FROM dbo.tblProblemControlTEMP ORDER BY ProjectID
DECLARE @ProjectID int
DECLARE @ProblemString nvarchar(500)
CREATE TABLE #tblProblemString(ProjectID int, ProblemString nvarchar(500))
SELECT @ProjectID = ProjectID FROM dbo.tblProblemControlTEMP
WHILE @@ROWCOUNT <> 0
BEGIN
WHILE @ProjectID = 17--(SELECT ProjectID FROM dbo.tblProblemControlTEMP)
BEGIN
SELECT @ProblemString = @ProblemString + ',' + (SELECT ProblemControlID FROM tblProblemControlTEMP)
INSERT INTO #tblProblemString VALUES (@ProjectID, @ProblemString)
END
END
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Have you considered that using a CURSOR is a lot slower than a set based query?
ASKER
Thanks for the help tigin44 , I eventually figured out that I needed to create a type of cylcing Do Loop with : Cursor & Fetch
Open in new window