Concatenate Query - 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 2005 Forums

Transact-SQL (2005)

Concatenate Query

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-07-01 : 04:19:04

Hi Guys

I am having a problem creating a concatenate using the following data.

StockCode, LineNumber, Text
ST00001,1,Excellence and opportunity: a science
ST00001,2,and innovation policy for the 21st century.
ST00001,3,Executive summary.|| Executive summary to White
ST00001,4,Paper 'Excellence and opportunity: a science and
ST00001,5,innovation policy for the 21st century' [Cm
ST00001,6,4814].
ST00002,1,Excellence and opportunity: a science
ST00002,2,and innovation policy for the 21st Century [Cm
ST00002,3,"4814].|| Command Paper 4814, the science and"
ST00002,4,innovation White Paper.
ST00003,1,Excellence and opportunity: a science
ST00003,2,and innovation policy for the 21st century.
ST00003,3,Foreword.|| Foreword for Excellence and
ST00003,4,opportunity - a science and innovation policy for
ST00003,5,the 21st century.

The thing I am trying to do is concatenate the values in the text fields together in a query. So the text item for line 1 joins line 2and so on. For example:

ST00001,1,Excellence and opportunity: a science
ST00001,2,and innovation policy for the 21st century.
ST00001,3,Executive summary.|| Executive summary to White
ST00001,4,Paper 'Excellence and opportunity: a science and
ST00001,5,innovation policy for the 21st century' [Cm
ST00001,6,4814].

Should become:
ST00001, Excellence and opportunity: a science and innovation policy for the 21st century. Executive summary.|| Executive summary to White...

Is this possible to do?

Thanks

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-07-01 : 04:29:47

do forum search on rowset string concatination
or check the links
http://www.codeproject.com/KB/reporting-services/Concatenate_Field_Values.aspx
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-01 : 04:30:34

is the field text of type varchar or text datatype?

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-07-01 : 04:35:32

Hi

At the moment it is nvarchar

Thanks

Kassem
Starting Member

7 Posts

Posted - 2008-07-01 : 04:52:48

try to Crearte a function Fns_concatenate(StockCode, LineNumber) as nvarchar

and open a currsor to this table to call the function in each row

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-01 : 04:56:09

quote:
Originally posted by rcr69er

Hi

At the moment it is nvarchar

Thanks

SELECT t.StockCode,RTRIM(tl.txtlist)
FROM
(SELECT DISTINCT StockCode FROM yourtable)t
CROSS APPLY(SELECT Text + ' ' AS [text()]
            FROM YourTable
            WHERE StockCode=t.StockCode
            FOR XML PATH(''))tl(txtlist)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-01 : 04:56:43

also refer to
http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-01 : 05:06:22

quote:
Originally posted by Kassem

try to Crearte a function Fns_concatenate(StockCode, LineNumber) as nvarchar

and open a currsor to this table to call the function in each row

No need of cursor. You can use FOR XML PATH construct in sql 2005 to get the result.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-01 : 05:08:40

"You can use FOR XML PATH construct in sql 2005 to get the result."
see the 2nd link i posted

KH
[spoiler]Time is always against us[/spoiler]

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-07-01 : 05:59:13

quote:

Originally posted by visakh16

quote:
Originally posted by rcr69er

Hi

At the moment it is nvarchar

Thanks

SELECT t.StockCode,RTRIM(tl.txtlist)
FROM
(SELECT DISTINCT StockCode FROM yourtable)t
CROSS APPLY(SELECT Text + ' ' AS [text()]
            FROM YourTable
            WHERE StockCode=t.StockCode
            FOR XML PATH(''))tl(txtlist)

Thats GREAT!!! Thanks for that :D

Now to my next problem. :( Now that I have the data like that I need to split the text field into two sepearate columns. Everything before the character || will go into a column called Title, everything on the right into a column called Description.

For example
Unique Stock Code,Text
ST00001,Excellence and opportunity: a science and innovation policy for the 21st century. Executive summary.|| Executive summary to White Paper 'Excellence and opportunity: a science and innovation policy for the 21st century' [Cm 4814].

Becomes:
Unique Stock Code, Title, Description
ST00001,Excellence and opportunity: a science and innovation policy for the 21st century. Executive summary.,Executive summary to White Paper 'Excellence and opportunity: a science and innovation policy for the 21st century' [Cm 4814].

Is it possible to do this all in one go?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-01 : 06:02:28

use fnParseString from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-01 : 06:18:31

Or:-

CREATE FUNCTION ParseValues  
(@String varchar(8000),
@Delimiter char(1)  
)  
RETURNS @RESULTS TABLE  
(ID int identity(1,1),  
Val varchar(1000)  
)  
AS  
BEGIN  
DECLARE @Value varchar(100)  
  
WHILE @String is not null  
BEGIN  
SELECT @Value=CASE WHEN CHARINDEX(@Delimiter,@String) >0 THEN LEFT(@String,CHARINDEX(@Delimiter,@String)-1) ELSE @String END,  
@String=CASE WHEN CHARINDEX(@Delimiter,@String) >0 THEN SUBSTRING(@String,CHARINDEX(@Delimiter,@String)+1,LEN(@String)) ELSE NULL END  
INSERT INTO @RESULTS (Val)   
SELECT @Value   
END  
RETURN  
END

use the above function and use it like this

SELECT t.Unique Stock Code,
MAX(CASE WHEN b.ID=1 THEN b.Val ELSE NULL END) AS Title,
MAX(CASE WHEN b.ID=2 THEN b.Val ELSE NULL END) AS Description
FROM YourTable t
CROSS APPLY dbo.ParseValues(t.Text,'||') b
GROUP BY t.Unique Stock Code

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-07-01 : 06:48:54

quote:

Originally posted by visakh16

Or:-

CREATE FUNCTION ParseValues  
(@String varchar(8000),
@Delimiter char(1)  
)  
RETURNS @RESULTS TABLE  
(ID int identity(1,1),  
Val varchar(1000)  
)  
AS  
BEGIN  
DECLARE @Value varchar(100)  
  
WHILE @String is not null  
BEGIN  
SELECT @Value=CASE WHEN CHARINDEX(@Delimiter,@String) >0 THEN LEFT(@String,CHARINDEX(@Delimiter,@String)-1) ELSE @String END,  
@String=CASE WHEN CHARINDEX(@Delimiter,@String) >0 THEN SUBSTRING(@String,CHARINDEX(@Delimiter,@String)+1,LEN(@String)) ELSE NULL END  
INSERT INTO @RESULTS (Val)   
SELECT @Value   
END  
RETURN  
END

use the above function and use it like this

SELECT t.Unique Stock Code,
MAX(CASE WHEN b.ID=1 THEN b.Val ELSE NULL END) AS Title,
MAX(CASE WHEN b.ID=2 THEN b.Val ELSE NULL END) AS Description
FROM YourTable t
CROSS APPLY dbo.ParseValues(t.Text,'||') b
GROUP BY t.Unique Stock Code

Hey, Thanks for that but it doesnt seem to be working.

I get the following result:
ST00001,Paper 'Excellence and opportunity: a science and,
ST00002,innovation White Paper.,
ST00003,the 21st century.,
...
...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-01 : 07:00:29

quote:

Originally posted by rcr69er

quote:

Originally posted by visakh16

Or:-

CREATE FUNCTION ParseValues  
(@String varchar(8000),
@Delimiter char(2)  
)  
RETURNS @RESULTS TABLE  
(ID int identity(1,1),  
Val varchar(1000)  
)  
AS  
BEGIN  
DECLARE @Value varchar(100)  
  
WHILE @String is not null  
BEGIN  
SELECT @Value=CASE WHEN CHARINDEX(@Delimiter,@String) >0 THEN LEFT(@String,CHARINDEX(@Delimiter,@String)-1) ELSE @String END,  
@String=CASE WHEN CHARINDEX(@Delimiter,@String) >0 THEN SUBSTRING(@String,CHARINDEX(@Delimiter,@String)+1,LEN(@String)) ELSE NULL END  
INSERT INTO @RESULTS (Val)   
SELECT @Value   
END  
RETURN  
END

use the above function and use it like this

SELECT t.Unique Stock Code,
MAX(CASE WHEN b.ID=1 THEN b.Val ELSE NULL END) AS Title,
MAX(CASE WHEN b.ID=2 THEN b.Val ELSE NULL END) AS Description
FROM YourTable t
CROSS APPLY dbo.ParseValues(t.Text,'||') b
GROUP BY t.Unique Stock Code

make it char(2) as you've two caharcaters for your delimiter.

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-07-01 : 07:34:56

Hi

Now it is showing:
ST00001,Paper 'Excellence and opportunity: a science and,| Executive summary to White
ST00002,innovation White Paper.,| Command Paper 4814, the science and
ST00003,the 21st century.,| Foreword for Excellence and

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources