I need a transact SQL statement that will do the following:

I have data grouped by a query like this:

ID      Status
128244	R
128244	C
128277	R
128277	C

I want to recombine this data into a single row:
ID      Status
128244  R,C
128277  R,C

Please base your reponse on the example provided.  The table name can
be called ExampleTable.

---

Request for Question Clarification by shivreddy-ga on 25 Nov 2002 08:13 PST

Hi,

Thank you for your question. I have looked up the GROUP BY clause and
came up with a little problem. I am not sure though, but please
clarify.

If you run the following code:

SELECT ID, Status
FROM ExampleTable
GROUP BY ID

You will NOT get the answer (please try anyway). However there is no
alternative because you are attempting to put all the rows from the
resultset into a single row. This will cause a problem. GROUP BY is
mainly used to group common types together and find out the (using
arithemietic functions) MAX or MIN or AVG of the row elements in the
group.

If you are sure this can be done anyway please let me know so that I
can check deeper.


Thanks!

Regards,
Shiv Reddy

---

Clarification of Question by pmclinn-ga on 25 Nov 2002 08:19 PST

I know this can be done in VB, so I'm assuming this can be done using
transact SQL. Group By is definitely not the answer.  [Group by will
indeed give you the same results I posted.]  I need a transact
statement that will regroup these common records into one row.

---

Request for Question Clarification by hammer-ga on 25 Nov 2002 08:46 PST

PMClinn,

You can't do this with straight T-SQL. You have two choices:

1. You can emulate what you do in VB by using a Stored Procedure. 

2. You can receive the data using your current T-SQL statement, then
munge it inside VB.

Would an example of either of these options be an acceptable answer?
If you want the Stored Procedure, what version of SQL Server are you
running? If you wznt the VB, is an ADO Recordset an acceptable final
result?
- Hammer

---

Clarification of Question by pmclinn-ga on 26 Nov 2002 07:27 PST

Could you show me how to do the  
"..emulate what you do in VB by using a Stored Procedure..."

I need to see the code in action so I can convert over some flat file systems.

The server is MS SQL Server 2000.

---

Request for Question Clarification by hammer-ga on 03 Dec 2002 06:05 PST

I am interested in answering your question, but I believe that, to
answer it well, your question will require more time and effort than
the
average amount of time and effort associated with $5.00. Here is a
link to
guidelines about pricing your question,   
  
https://answers.google.com/answers/pricing.html 

If you both raise your price and also post a clarification here, the
system will notify me and I will take another look at your question.

Try something like this. It is written to run against the NorthWind
database.
-------------------------------------------------------------------------------

CREATE PROCEDURE TestMe AS

	SET NOCOUNT ON	

	DECLARE @comp_id INTEGER
	DECLARE @emp_id  INTEGER
	DECLARE @terr_id  INTEGER
	DECLARE @curr_id INTEGER
	DECLARE @terr VARCHAR(4096)

	CREATE TABLE #stack
	(
		stack_nbr		INTEGER IDENTITY NOT NULL PRIMARY KEY,
		employee_id		INTEGER NOT NULL,
		territory_id		INTEGER NULL, 
	)

	CREATE TABLE #results
	(
		result_nbr		INTEGER IDENTITY NOT NULL PRIMARY KEY,
		employee_id		INTEGER NOT NULL,
		territory			VARCHAR(4096), 
	)

	INSERT INTO #stack
		SELECT EmployeeID, TerritoryID
		FROM EmployeeTerritories
		ORDER BY EmployeeID, TerritoryID

	SELECT @comp_id = 0
	SELECT @terr = ''

	-- process items in stack table
	WHILE EXISTS(SELECT * FROM #stack)
	BEGIN
		SET ROWCOUNT 1	-- select the first item from the stack
		SELECT  @curr_id = stack_nbr, @emp_id = employee_id, @terr_id =
territory_id FROM #stack
		IF @comp_id = 0
		BEGIN
			SELECT @terr = CAST(@terr_id AS VARCHAR(256))
			SELECT @comp_id = @emp_id
		END
		ELSE
		BEGIN
			IF @comp_id = @emp_id
			BEGIN
				SELECT @terr = @terr + ', ' + CAST(@terr_id AS VARCHAR(256))
			END
			ELSE
			BEGIN
				INSERT INTO #results
				VALUES (@comp_id, @terr)
				SELECT @terr = CAST(@terr_id AS VARCHAR(256))
				SELECT @comp_id = @emp_id
			END
		END
		DELETE FROM #stack WHERE stack_nbr = @curr_id
	END

	INSERT INTO #results
	VALUES (@comp_id, @terr)
	
	SET ROWCOUNT 0

	SELECT * 
	FROM #results
	ORDER BY employee_id
	
	DROP TABLE #stack
	DROP TABLE #results

	SET NOCOUNT OFF

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

- Hammer

This is relative easy with a stored procedure but imposible with group by

Up'd it to 10