SUM function for string???? - 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

General SQL Server Forums

Database Design and Application Architecture

SUM function for string????

Author

Topic

mightypenny_ph
Yak Posting Veteran

54 Posts

Posted - 2007-08-16 : 10:32:39

hi kristen or anybody there,

is there a way you can concat string columns in a select statement... i have two tables (joined) and i wanted to display a concatenated values.

data details:
table 1
pk_transactionid transactiondate and so on
1 1/1/2007 *********
2 1/2/2007 *********
3 1/3/2007 *********

table 2
pk_rowid fk_transactionid description
1 1 record1
2 2 record2
3 3 record3
4 1 record4
5 2 record5
6 1 record6

result set should be like this
pk_transactionid transactiondate description --> this is concatenated
1 1/1/2007 record1, record4, record6
2 1/2/2007 record2, record5
3 1/3/2007 record 6

thanks

SlayerS_`BoxeR` + [ReD]NaDa

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-16 : 10:34:17

see http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx

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

mightypenny_ph
Yak Posting Veteran

54 Posts

Posted - 2007-08-16 : 10:44:49

thank you for the quick response. I appreciate it. i think we'll be using the scalar udf without using either a temp table or a cursor... :)

however, i think this will only concat 1record at a time... i think this will not solve my problem where i need to do a SELECT statement from table 1 and get the concatenated value from table 2 right away..

SlayerS_`BoxeR` + [ReD]NaDa

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-16 : 11:25:53

have you try it out ?

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-16 : 11:26:50

[code]CREATE FUNCTION fn_concat(@pk_transactionid int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @s varchar(8000)
SELECT @s = ISNULL(@s + ',', '') + [description]
FROM table2 t2
WHERE t2.fk_transactionid = @pk_transactionid
ORDER BY [description]

RETURN @s
END

-- Example Run
SELECT *, dbo.fn_concat(pk_transactionid)
FROM table1[/code]

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

mightypenny_ph
Yak Posting Veteran

54 Posts

Posted - 2007-08-16 : 11:42:11

cool!!! hehehehe... thanks you khtan... got it... it's now working.. :p

SlayerS_`BoxeR` + [ReD]NaDa

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-16 : 13:06:23

If you are using SQL Server 2005, the FOR XML PATH approach is faster.

E 12°55'05.25"
N 56°04'39.16"

mightypenny_ph
Yak Posting Veteran

54 Posts

Posted - 2007-08-16 : 13:18:10

yes, i'm using sql server 2005.. i will look in to that also.. tnx...

SlayerS_`BoxeR` + [ReD]NaDa

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-16 : 15:43:03

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

E 12°55'05.25"
N 56°04'39.16"

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-16 : 15:50:53

there are many similar posts you can even find many functions on the net. Cheers!

Ashley Rhodes

mightypenny_ph
Yak Posting Veteran

54 Posts

Posted - 2007-08-16 : 16:05:12

nice STUFF :) hehehehe

SlayerS_`BoxeR` + [ReD]NaDa

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-16 : 16:17:07

Thanks.

E 12°55'05.25"
N 56°04'39.16"

mightypenny_ph
Yak Posting Veteran

54 Posts

Posted - 2007-08-17 : 18:00:27

-- FROM PROVIDED LINK ABOVE

-- Prepare sample data
DECLARE @Sample TABLE (ID INT, Code VARCHAR(3))

INSERT @Sample
SELECT 290780, 'LT' UNION ALL
SELECT 290780, 'AY' UNION ALL
SELECT 290781, 'ILS' UNION ALL
SELECT 290780, 'AY'

-- Show the expected output
SELECT DISTINCT s1.ID,
STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
FROM @Sample AS s1
ORDER BY s1.ID

SELECT DISTINCT s1.ID,
STUFF((SELECT TOP 100 PERCENT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID ORDER BY ',' + s2.CODE FOR XML PATH('')), 1, 1, '') AS CODES
FROM @Sample AS s1
ORDER BY s1.ID

SELECT DISTINCT s1.ID,
STUFF((SELECT ',' + s2.CODE FROM @Sample AS s2 WHERE s2.ID = s1.ID FOR XML PATH('')), 1, 1, '') AS CODES
FROM @Sample AS s1
ORDER BY s1.ID

SlayerS_`BoxeR` + [ReD]NaDa

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources