May 15, 2006 at 3:13 pm
My data looks like this:
empno name union accrual
________________________________
12345 jones,brian cdd prsnl40
12345 jones,brian cdd vac40
12345 jones,brian cdd sick40
what I want is all the data in line
12345 jones,brian cdd prsnl40 vac40 sick40
So, three records into one. TSQL?. Do I need to create a stored procedure and load up a new table or what are my options? I'm new (today) with the whole stored procedure world. Not really sure what I can and can't do with them...
May 16, 2006 at 6:37 am
You'll want a User-Defined Function to concatenate the values.
CREATE FUNCTION ConcatAccrual (@vEmp int)
RETURNS varchar(500)
AS
BEGIN
DECLARE @myString varchar(500)
SET @myString = ''
SELECT @myString = @myString + EmpTable.Accrual + ' '
FROM EmpTable
WHERE EmpTable.EmpNo = @vEmp
RETURN RTRIM(@myString)
END
SELECT EmpNo, [Name], [Union], ConcatAccrual(EmpNo) AS AccrualList
FROM EmpTable
GROUP BY EmpNo, [Name], [Union]
As an additional note, you may want to look into naming conventions. Both NAME and UNION are reserved words in SQL. If you ever try to use them without using the square brackets around them, you will have severe problems. Try using EmpName and EmpUnion instead.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply