concatenat string resolved - 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)

concatenat string resolved

Author

Topic

Gekko
Yak Posting Veteran

63 Posts

Posted - 2011-06-17 : 10:58:23

Hallo ....

I apologize to my previous topic was poorly explained, describes..

I have 4 tables.
where:

CodeProduct.ID=Product.ID
CodeProduct.Code=Awards.Code
Custom.NumCustom=Awards.NumCustom

I have 4 tables:

CodeProduct
Id....Code
1-----100
2-----200
3-----300
4-----400
5-----500

Product
Id.....Name
1-------a
2-------b
3-------c
4-------d
5-------e

Awards
Code.....NumCustom.....Awards
100------1------------10,00
100------1------------20,00
200------1------------15,00
200------1------------20,00
300------2------------10,00
300------2------------10,00
300------2------------20,00
300------2------------20,00
500------2------------10,00
500------2------------30,00

result:

Custom
Numcustom....Note
1-----------a 10, b 15
2-----------c 10, e 10

big thanks

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-06-17 : 11:36:04

It's customary to include the question that you want answered in your post.

CODO ERGO SUM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-17 : 11:50:27

let me consult my crystal ball . . .


; with
data as
(
	select	a.NumCustom, p.Name, Awards = min(Awards)
	from	Awards a
		inner join CodeProduct cp	on a.Code	= cp.Code
		inner join Product p		on cp.Id	= p.Id
	group by a.NumCustom, p.Name
)
select	NumCustom,
	Note 	= stuff	(
			(
				select	',' + Name + ' ' + convert(varchar(10), x.Awards)
				from	data x
				where	x.NumCustom	= d.NumCustom
				for xml path('')
			),
			1, 1, '')
from	data d
group by NumCustom

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

Gekko
Yak Posting Veteran

63 Posts

Posted - 2011-06-17 : 13:18:32

ok khtan thank you...it is good.

I have small problem XML path I dont use....(return memo) 2 words (WideMemo)
Therefore, I beseech thee.
First section query from you is good.: Now I need last new query...

Register
NumCust...Group.....discount
1----------A----------10,00
1----------B----------10,00
1----------C----------10,00
2----------A----------10,00
2----------B----------20,00
2----------C----------30,00
3----------A----------20,00
3----------B----------20,00
3----------C----------20,00

Customer
NumCust
1
2
3
4
5

result:

Customer
NumCust....Reduction
1---------A 10, B 10, C 10
2---------A 10, B 20, C 30
3---------A 20, B 20, C 20
4---------
5---------

thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-06-18 : 14:36:19

How about you try one of these solutions: http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

Gekko
Yak Posting Veteran

63 Posts

Posted - 2011-06-18 : 17:22:09

thanks for url but it is no for me solution because I have lot of rows
I looked over many topics but each has xml path

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-06-18 : 17:40:39

There are at least 6 other techniques besides XML PATH described in that article. And why exactly can't you use XML PATH?

Gekko
Yak Posting Veteran

63 Posts

Posted - 2011-06-18 : 17:45:54

thanks for answer..
I have small problem with XML path I dont use because (return memo) 2 words (WideMemo) is result row
comforted me but I do not find six techniques

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-19 : 03:58:16

what do you mean by "2 words (WideMemo)" ?

Anyway, according to the link, the 6 other techniques that does not uses XML

quote:

Concatenating values when the number of items is not known.
Recursive CTE methods
The blackbox XML methods
Using Common Language Runtime.
Scalar UDF with recursion.
Table valued UDF with a WHILE loop.
Dynamic SQL
The Cursor approach.

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

Gekko
Yak Posting Veteran

63 Posts

Posted - 2011-06-19 : 12:18:07

thanks khtan
here is result your quote with xml path

NumCust....Note
1.......(WideMemo)
2.......(WideMemo)
3.......(WideMemo)
4.......(WideMemo)
5.......(WideMemo)

That is why I am looking for another solution.
khtan pleas try writening else 1 quote with this data, (with xml path can by). I'll try. maybe it goes with this tab
I'll be very grateful to you for else one quote....

Register
NumCust...Group.....discount
1----------A----------10,00
1----------B----------10,00
1----------C----------10,00
2----------A----------10,00
2----------B----------20,00
2----------C----------30,00
3----------A----------20,00
3----------B----------20,00
3----------C----------20,00

Customer
NumCust
1
2
3
4
5

result:

Customer
NumCust....Reduction
1---------A 10, B 10, C 10
2---------A 10, B 20, C 30
3---------A 20, B 20, C 20
4---------
5---------

really thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-19 : 12:21:33

use convert() to convert the result to varchar ?

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

Gekko
Yak Posting Veteran

63 Posts

Posted - 2011-06-19 : 12:37:48

already it`s ok....
but varchar return 10.00, 20.00 etc...I want 10, 20 etc. varchar does not work (not return 10, 20, 30..etc) What should I use? Float? I do not know. I trying
khtan thanks...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-19 : 19:47:28

what is the data type for Awards ?

try convert to integer before convert to string


select	',' + Name + ' ' + convert(varchar(10), convert(int, x.Awards))

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

Gekko
Yak Posting Veteran

63 Posts

Posted - 2011-06-20 : 01:17:08

You were right, this is correct (int)
thanks for resolved and patience .....

nevertheless I have a question, when I use CAST (on convert) IT will like this? (this my quote does not worked)

select NumCustom,
Note = CAST( stuff ((
select ',' + Name + ' ' + convert(varchar(10), x.Awards) as varchar(255)
from data x
where x.NumCustom = d.NumCustom
for xml path('')
),
1, 1, '')
from data d
group by NumCustom

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-20 : 08:17:32

the cast is for the entire query no the column in the select statement


Note = CAST( stuff (
                    ( select ',' + Name + ' ' + convert(varchar(10), x.Awards)
                      from   data x
                      where  x.NumCustom = d.NumCustom
                      for xml path('') ),
                    1, 1, '')  as varchar(255) )

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

Gekko
Yak Posting Veteran

63 Posts

Posted - 2011-06-20 : 09:34:22

that's exactly it.
khtan my respect, big thanks
*************closed tema********************************

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources