Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

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.
 
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

Open in new window

0
PAGANED
Asked:
PAGANED
  • 4
  • 2
2 Solutions
 
tigin44Commented:
try this

SELECT ProjectID, ( SELECT ProblemControlID + ','
                              FROM yourTable A
                              WHERE A.ProjectID = B.ProjectID
                              FOR XML PATH (''))  AS ProblemString
FROM yourTable B
GROUP BY B.ProjectID
0
 
tigin44Commented:
and this one eliminates the extra commas...
SELECT ProjectID, STUFF( (SELECT ',' + ProblemControlID  
					FROM yourTable A
					WHERE A.ProjectID = B.ProjectID
					FOR XML PATH ('')), 1,1,'')  AS ProblemString
FROM yourTable B
GROUP BY B.ProjectID

Open in new window

0
 
PAGANEDAuthor Commented:
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
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
PAGANEDAuthor Commented:
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

Open in new window

0
 
PAGANEDAuthor Commented:
Finally Figured this problem out with a procedure
.
Got away from the VBA code which was WAY TOO Slow
.
Ended up needing to create a Declare & Fetch
.
DECLARE cur_Problem CURSOR FOR
SELECT Distinct ProblemControlID
FROM tblProblemControl
WHERE ProblemControlSQL Is Not Null
ORDER BY ProblemControlID

OPEN cur_Problem

FETCH NEXT FROM cur_Problem
INTO @ProblemControlID

WHILE @@FETCH_STATUS = 0
BEGIN
0
 
Anthony PerkinsCommented:
Have you considered that using a CURSOR is a lot slower than a set based query?
0
 
PAGANEDAuthor Commented:
Thanks for the help tigin44 , I eventually figured out that I needed to create a type of cylcing Do Loop with :  Cursor & Fetch
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now