[dba-SQLServer] unnormalize data

This just kicked my butt yesterday:) I needed to sum up some adjustments and
concatenate the reasons. This procedure using a loop and table variable work
faster than a cursor (which is another way you could do this).

Substitute
	my "AdjReason" with your "Task"
	my "MachineType" with your "Objective"
	my #tblTempMach with your table name

Hope this helps.

David


DECLARE @RowCnt AS INT
DECLARE @MaxRows AS INT
DECLARE @AdjR AS VARCHAR(1000)
SET @AdjR = ''
SET @RowCnt = 1
--Initialize a table variable, basically SQL's version of an Array
DECLARE @Temp Table
	(RowNum INT IDENTITY (1,1) PRIMARY KEY NOT NULL,
	 Model VARCHAR(20) NULL,
	 Notes VARCHAR(1000) NULL)
--Insert records into the table variable by selecting them from some
location
INSERT INTO @Temp (Model)
		SELECT DISTINCT MachineType FROM #tblTempMach WHERE AdjReason IS NOT NULL
--Count the number of rows so we no how many times we need to loop
SELECT @MaxRows = COUNT(*) FROM @Temp

WHILE @RowCnt <= @MaxRows
BEGIN
	SELECT
		@AdjR = AdjReason + ', ' + @AdjR --This is the concatention
	FROM #tblTempMach
		INNER JOIN @Temp
			ON #tblTempMach.MachineType = Model
	WHERE AdjReason IS NOT NULL AND #tblTempMach.MachineType = model

	 UPDATE @Temp
	   SET Notes = @AdjR

	   SET @AdjR =''
	SET @RowCnt = @RowCnt + 1
END

UPDATE @Temp
	SET Notes = LEFT(Notes, LEN(Notes)-1)


SET NOCOUNT OFF


--Select it all out at the end
SELECT * FROM @Temp

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Clay
Passick
Sent: Thursday, January 20, 2005 12:24 PM
To: dba-sqlserver at databaseadvisors.com
Subject: [dba-SQLServer] unnormalize data


Is there a way in SQL 2000 to unormalize data that exist.   I have done
this in MS Access but never with SQL.  The fields are in the same table.
   I have used views to some extent but my experience with sprocs is
rather limited.   The data is being viewed on the web.  The following is
a quick example of what I would like to do.

>From this:

Objective		Task
A		1
A		2
B		1
B		2
B		3

To this:

Objective		Task
A		1 2 or 1,2
B		1 2 3 or 1,2,3

TIA
Clay