Concatenate results and insert into field 1

[markknowsley]

Imagine I have the following table:

|*******|*******|*********|
| EmpID | Value | History |
|*******|*******|*********|
| 000001| 13.62 | |
| 000001| 20.22 | |
| 000001| 12.90 | |
| 000004| 18.11 | |
| 000004| 30.01 | |
| 000004| 15.90 | |
|*******|*******|*********|

(doesn't line up perfectly, sorry, but you get the idea )

What I want to do is write a SQL statement that will concatenate the results values for each seperate EmpID and insert these values into the History column. So, where the EmpID is 000001 I want to see '13.62, 20.22, 12.90' in all three History fields where EmpID = 000001 and '18.11, 30.01, 15.90 in all three History fields where EmpID = '000004'.

I've worked out how to generate the string using the COALESCE command in SQL, what I'm struggling to do is work out how I can run the command for each row in the database table.

Any help much appreciated.

Mark.

 

[gmmastros]

Take a look at this thread... thread183-1159740

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[markknowsley]

So using the example from the thread you referenced:

SELECT @result = @result + company_name + ', '
FROM companies
WHERE 'your conditions here'

SELECT @result = SUBSTRING(@result, 1, LEN(@result) - 1)

SELECT @result

What I can't get my head around is how to make this insert the correct result for each row. I've just tried to run the above without a where clause and I think it's trying to truncate every row in the table and insert it into the column and failing. How do I iterate through each row and insert the correct empid for that row as the WHERE clause?

 

[gmmastros]

Read my post dated 2 Dec 05 11:16 .

In this post, I suggest a user defined function which will return the proper data. I even show an example of how this function would be called. Granted, it's a select, but it should be simple enough to convert it to an insert (or update) depending on the situation.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Here's mu suggestion....

Create a user defined function (based on the referenced thread) that will concatenate the value column (for each EmpId).

[COLOR=blue]Create[/color] [COLOR=#FF00FF]Function[/color] GetEmployeeHistory
	(@EmpId [COLOR=blue]VarChar[/color](10))
Returns [COLOR=blue]VarChar[/color](1000)
[COLOR=blue]As[/color]
[COLOR=blue]Begin[/color]

	[COLOR=blue]Declare[/color] @result [COLOR=blue]VarChar[/color](1000)
	[COLOR=blue]Set[/color] @Result = [COLOR=red]''[/color]

	[COLOR=blue]SELECT[/color] @result = @result + [COLOR=#FF00FF]Convert[/color]([COLOR=blue]VarChar[/color](30), [COLOR=blue]Value[/color]) + [COLOR=red]','[/color]
	[COLOR=blue]FROM[/color]   [!]TableName[/!]
	[COLOR=blue]Where[/color]  EmpId = @EmpId
	
	[COLOR=blue]If[/color] [COLOR=#FF00FF]Right[/color](@Result, 1) = [COLOR=red]','[/color]
		[COLOR=blue]Set[/color] @Result = [COLOR=#FF00FF]Left[/color](@Result, Len(@Result)-1)

	[COLOR=blue]Return[/color] @Result
[COLOR=blue]End[/color]

Then, it's a simple matter to update the original table. Like this...

[COLOR=blue]Update[/color] [!]TableName[/!] [COLOR=blue]Set[/color] History = dbo.GetEmployeeHistory(EmpId)

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[markknowsley]

That's brilliant - just what I wanted. My SQL programming skills leave a lot to be desired.

 

[Lamprey13]

markknowsley, I assume you want to do this for academic reasons. However, if this is something that is "real" let us know and I'm sure we can help you put together the correct table structure to capture a history.