concatenation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

concatenation

Author

Topic

psystems
Starting Member

8 Posts

Posted - 2005-08-04 : 15:31:41

Hi,

I need to concatenate a column from multiple rows into a single column in a new table.

How can I do this??

Thank you,

Stalker
Yak Posting Veteran

80 Posts

Posted - 2005-08-04 : 15:54:12

insert into NewTable
select column1+column2+column3 from OriginalTable

did you want this ?

psystems
Starting Member

8 Posts

Posted - 2005-08-04 : 16:02:19

I think I need something more like a loop because it will always be column 2 from each row that I need to concatenate.
Example:

123 test
123 abc
123 tap

result wanted is: 123|test,abc,tap

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-08-04 : 16:04:55

http://www.sqlteam.com/item.asp?ItemID=11021

Tara

psystems
Starting Member

8 Posts

Posted - 2005-08-05 : 16:22:39

Thank you for the link. I am still having problems with concatenating the column. I have inserted my code below.
WorkRecords will contain 1 row and there will be multiple corresponding WorkTasks records. The update statement only updates the resultvalues column with 1 of the Worktasks records. I have also included an example.
Any help is appreciated....

WorkRecords record
recordkey
-----------
2671

WOrkTasks records

recordkey resultvalue
----------- --------------------------------------------------
2671 Spoke with Carrier Rep
2671 John Doe
2671 Passed Timley Filing
2671 Proof sent
2671 Yes

Desired result

2671 Spoke with carrier Rep, Joh Doe, Passed Timley Filing, Proof sent, Yes

drop table #tmpresults
select distinct r.recordkey as 'Key', r.*
into #tmpResults
from workrecords as r
inner join worktasks as t on r.recordkey = t.recordkey
where t.dateworked is not null
order by r.recordkey

alter table #tmpresults
add resultvalues varchar(1000)

/*-----------------------------------------------------------*/
declare
@list varchar(8000),
@lasti int

select
@list = '',
@lasti = -1

update
#tmpResults
set
@list = ResultValues = case
when @lasti <> t.RecordKey then t.ResultValue
else @list + ', ' + t.ResultValue
end,
@lasti = t.recordKey

FROM #tmpResults as o
inner join worktasks as t on o.recordkey = t.recordkey
where t.resultvalue is not null

sqldan
Starting Member

1 Post

Posted - 2005-08-05 : 17:31:53

The following code may not solve your problem totally, but it shows you how to concatenate from a set of results into a variable. You can then use that variable to update.

CREATE TABLE WorkRecords (recordkey INT NOT NULL)
GO
INSERT INTO WorkRecords (recordkey) values (2671)
GO
CREATE TABLE WorkTasks (recordkey INT NOT NULL,resultvalue varchar(100))
GO
INSERT INTO WorkTasks (recordkey,resultvalue)
SELECT 2671, 'Spoke with Carrier Rep'
UNION
SELECT 2671, 'John Doe'
UNION
SELECT 2671, 'Passed Timley Filing'
UNION
SELECT 2671, 'Proof sent'
UNION
SELECT 2671, 'Yes'
GO
DECLARE @ResultList VARCHAR(8000)

SET @ResultList='2671'

SELECT @ResultList=@ResultList+', '+resultvalue FROM WorkTasks

PRINT @ResultList
GO

The resulting output from the print statement is:

2671, John Doe, Passed Timley Filing, Proof sent, Spoke with Carrier Rep, Yes

The import point in the above is that the variable must be initialised either to an empty string or as above '2671'.

Dan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-06 : 01:22:32

One more method

Declare @t table(recordkey int, resultvalue varchar(50))
Declare @s varchar(2000)
Declare @recordkey varchar(10)
set @s=''
Insert into @t values(2671,'Spoke with Carrier Rep')
Insert into @t values(2671,'John Doe')
Insert into @t values(2671,'Passed Timley Filing')
Insert into @t values(2671,'Proof sent')
Insert into @t values(2671,'yes')
Select @recordkey=recordkey from @t
Select @s=@s+' '+ resultvalue from @t
Select @recordkey+''+ @s

For multiple recordkeys, use function as suggested here
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-08-06 : 03:07:30

I favour the function route too - works nicely with multi-row record sets


USE Northwind
GO
CREATE FUNCTION dbo.ConcatOrderProducts(@OrderID int)
RETURNS VARCHAR(8000)
AS
BEGIN
	DECLARE @Output VARCHAR(8000)
	SELECT @Output = COALESCE(@Output+', ', '') + CONVERT(varchar(20), P.ProductName)
	FROM	dbo.[Order Details] OD
		JOIN dbo.Products P
			ON P.ProductID = OD.ProductID
	WHERE	OD.OrderID = @OrderID
	ORDER BY P.ProductName

	RETURN @Output
END
GO

SELECT OrderID, CustomerID, dbo.ConcatOrderProducts(OrderID)
FROM Orders
GO
DROP FUNCTION dbo.ConcatOrderProducts
GO

Kristen

psystems
Starting Member

8 Posts

Posted - 2005-08-08 : 11:00:32

Thanks for everyone's response. Kristen using the function makes the most sense to me and I have it working!

Thank you!!!

Dennis Falls
Starting Member

41 Posts

Posted - 2005-08-10 : 15:02:07

Wow, this function works very well. I was previously using a cursor to do the same thing, which does not work very well if you are trying to run a web report.

Are there any pitfalls I need to be aware of using this function?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-27 : 07:22:58

>>Are there any pitfalls I need to be aware of using this function?

If the concatenated string exceeds more than 8000 characters, you need to use more than one variable.
If you use front end application, do concatenation there

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-11-27 : 08:23:05

"exceeds more than 8000 characters ..."
Slightly different purpose, but if you have that problem it may give you a clue:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=dynamic+SQL+that+is+longer+than

"Are there any pitfalls I need to be aware of using this function?"

I think there may be a problem with it honouring the ORDER BY in SQL2005

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-27 : 09:49:22

>>I think there may be a problem with it honouring the ORDER BY in SQL2005

Will it give wrong result or truncate the data?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-11-27 : 10:39:38

Just get one value (last one presumably) IIRC

Kristen

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2006-11-27 : 13:26:29

quote:

If you use front end application, do concatenation there

Why? It works fine in the database... and do you have a code snippet to show how you would do it in an app? Any language would be fine... After listening to folks say "do it in the app" for years, I've never had anyone show me an example of how to "do it in the app" and I'd just like to see how it's done once (I'm mostly a data troll and I don't very often even get to see the app code)...

--Jeff Moden

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-27 : 13:37:08

quote:
I'd just like to see how it's done once

SqlConnection conn = new SqlConnection("data source=(local);initial catalog=Northwind;integrated security=SSPI");
SqlCommand cmd = new SqlCommand("SELECT CustomerID FROM Customers", conn);
string ConcatenatedString = "";
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
	ConcatenatedString += dr.GetString(0); //adjust this to add in separators as required
}
dr.Close();
conn.Close();

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-21 : 02:29:19

quote:
Originally posted by Jeff Moden

quote:

If you use front end application, do concatenation there

Why? It works fine in the database... and do you have a code snippet to show how you would do it in an app? Any language would be fine... After listening to folks say "do it in the app" for years, I've never had anyone show me an example of how to "do it in the app" and I'd just like to see how it's done once (I'm mostly a data troll and I don't very often even get to see the app code)...

--Jeff Moden

How did I fotget to answer your question?

The above is .NET code

The following is VB6 code


Set Rs=Con.Execute("Select name from table")
Dim St as string
While not Rs.eof
    St=St&Rs("Name")&","
    Set Rs=Rs.moveNext
Loop

Now Left(St,len(st)-1) will give all concatenated names seperated by comma

In Reports like Crystal Reports, it is possible with using Formulae

I didnt say it cant be done in sql.

Read my first reply that I have given query with example and also related Link.

My Do it app reply was on the question
Are there any pitfalls I need to be aware of using this function?
If the questioner uses the front end application, it is simple to concatenate there

Also most of the time, they have Front end application

They are not simply running queries and export concatenated data to TEXT File

I hope you understand What I am trying to say

Madhivanan

Failing to plan is Planning to fail

ravensensei
Starting Member

13 Posts

Posted - 2007-06-26 : 17:53:42

quote:

Originally posted by Kristen

I favour the function route too - works nicely with multi-row record sets


USE Northwind
GO
CREATE FUNCTION dbo.ConcatOrderProducts(@OrderID int)
RETURNS VARCHAR(8000)
AS
BEGIN
	DECLARE @Output VARCHAR(8000)
	SELECT @Output = COALESCE(@Output+', ', '') + CONVERT(varchar(20), P.ProductName)
	FROM	dbo.[Order Details] OD
		JOIN dbo.Products P
			ON P.ProductID = OD.ProductID
	WHERE	OD.OrderID = @OrderID
	ORDER BY P.ProductName

	RETURN @Output
END
GO

SELECT OrderID, CustomerID, dbo.ConcatOrderProducts(OrderID)
FROM Orders
GO
DROP FUNCTION dbo.ConcatOrderProducts
GO

Kristen

Do you accept the blood of a first born child? OMG, I've been trying to get this to work somehow for over a week and you have the simplest answer right here! THANK YOU!!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-27 : 00:28:22

Also read the link I posted in my first reply

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-06-27 : 02:57:05

"Do you accept the blood of a first born child?"

I'm a consultant. Of course I do

Note that the issue I raised, above, that this may not work under SQL 2005. (in compatibility mode 90)

Kristen

ravensensei
Starting Member

13 Posts

Posted - 2007-06-27 : 08:33:04

quote:
Originally posted by Kristen

"Do you accept the blood of a first born child?"

I'm a consultant. Of course I do

Note that the issue I raised, above, that this may not work under SQL 2005. (in compatibility mode 90)

Kristen

This is mostly a bandaid for old data that isn't being moved from it's 2000 box.

Thanks for the heads up tho.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources