SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 concatenation
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

psystems
Starting Member

8 Posts

Posted - 08/04/2005 :  15:31:41  Show Profile  Reply with Quote
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

Russia
80 Posts

Posted - 08/04/2005 :  15:54:12  Show Profile  Reply with Quote
insert into NewTable
select column1+column2+column3 from OriginalTable

did you want this ?
Go to Top of Page

psystems
Starting Member

8 Posts

Posted - 08/04/2005 :  16:02:19  Show Profile  Reply with Quote
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

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36949 Posts

Posted - 08/04/2005 :  16:04:55  Show Profile  Visit tkizer's Homepage  Reply with Quote
http://www.sqlteam.com/item.asp?ItemID=11021

Tara
Go to Top of Page

psystems
Starting Member

8 Posts

Posted - 08/05/2005 :  16:22:39  Show Profile  Reply with Quote
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

Go to Top of Page

sqldan
Starting Member

United Kingdom
1 Posts

Posted - 08/05/2005 :  17:31:53  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 08/06/2005 :  01:22:32  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

Edited by - madhivanan on 08/06/2005 01:36:16
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/06/2005 :  03:07:30  Show Profile  Reply with Quote
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
Go to Top of Page

psystems
Starting Member

8 Posts

Posted - 08/08/2005 :  11:00:32  Show Profile  Reply with Quote
Thanks for everyone's response. Kristen using the function makes the most sense to me and I have it working!

Thank you!!!
Go to Top of Page

Dennis Falls
Starting Member

USA
41 Posts

Posted - 08/10/2005 :  15:02:07  Show Profile  Reply with Quote
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?
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 11/27/2006 :  07:22:58  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
>>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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/27/2006 :  08:23:05  Show Profile  Reply with Quote
"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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 11/27/2006 :  09:49:22  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
>>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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/27/2006 :  10:39:38  Show Profile  Reply with Quote
Just get one value (last one presumably) IIRC

Kristen
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 11/27/2006 :  13:26:29  Show Profile  Reply with Quote
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
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 11/27/2006 :  13:37:08  Show Profile  Reply with Quote
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();

Edited by - snSQL on 11/27/2006 13:38:06
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 12/21/2006 :  02:29:19  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

ravensensei
Starting Member

13 Posts

Posted - 06/26/2007 :  17:53:42  Show Profile  Reply with Quote
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!!!
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 06/27/2007 :  00:28:22  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Also read the link I posted in my first reply

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 06/27/2007 :  02:57:05  Show Profile  Reply with Quote
"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
Go to Top of Page

ravensensei
Starting Member

13 Posts

Posted - 06/27/2007 :  08:33:04  Show Profile  Reply with Quote
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.
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000