how to More than one column line joining together thanks

1：Is taking out only after and remark is empty and then string concatenation

for example: (now i need user ASA ) SQL:

drop table #temp_1

create table #temp_1(remark varchar(100),trans_no varchar(100))

insert into #temp_1 select * from (

select ' ' as remark, 'FH001' as trans_no union all

select 'test1' as remark, 'FH001' as trans_no union all

select 'test2' as remark, 'FH002' as trans_no union all

select 'test3' as remark, 'FH003' as trans_no union all

select 'test4' as remark, 'FH004' as trans_no )a

SQL： SELECT remark AS new_remark


FROM (
SELECT (

    SELECT Trans_no+'+"'+REmark+'"+'

    FROM (

        SELECT REmark,Trans_no

        FROM #Temp_1

        where Remark is not null and remark <>''

        GROUP BY REmark,Trans_no

    ) a

    FOR XML PATH('')) AS remark

) b

result (Merger and took out the only "trans_no")：

FH001+"test1"+FH002+"test2"+FH003+"test3"+FH004+"test4"+

column

asked 01 Sep '13, 04:17

mfkpie8
238●64●68●72
accept rate: 12%

edited 02 Sep '13, 11:14

I want to take the result of the unique value and the only connection, as "SQL XML PATH" More than one column line connection thanks result: FH001+"te1" FH001+"te1" FH002+"te2" FH003+"te3" FH004+"te4" thanks

(01 Sep '13, 06:59) mfkpie8

Do you want the result set to be a single row containing a single string that looks like this?

'FH001+"te1" FH001+"te1" FH002+"te2" FH003+"te3" FH004+"te4"'

If not, then please show us EXACTLY what you want the result set to look like.

Also explain how you want rows to be selected; your result omits 2 of the 7 rows in XM001, and it includes two copies of the TE1 FH001 row.

(02 Sep '13, 07:30) Breck Carter

Comment Text Removed

i modify my question! see my question please!thank

(02 Sep '13, 11:09) mfkpie8

Comment Text Removed

Does the pseudo-answer differ from the edited question? If not, I'd suggest to delete it.

Don't get me wrong but it's not really easy to understand your postings...

(02 Sep '13, 11:39) Volker Barth

flat view

3 Answers:

active answers oldest answers newest answers popular answers

I'm not sure quite what you are trying to do, but I think you need the LIST() function.

This (un-tested) example may help explain:

create table stuff(ID int default autoincrement, remark char(100), primary key (ID));
insert into stuff(remark) values('aa');
insert into stuff(remark) values('cc');
insert into stuff(remark) values('ff');
insert into stuff(remark) values('dd');
insert into stuff(remark) values('bb');
insert into stuff(remark) values('ee');

select LIST(remark order by ID) from stuff;
-- will return: 'aa,cc,ff,dd,bb,ee' as a single string value

select LIST(remark order by remark) from stuff;
-- will return: 'aa,bb,cc,dd,ee,ff' as a single string value

Is that what you are wanting to do?

permanent link

answered 01 Sep '13, 05:53

Justin Willey
7.5k●136●177●247
accept rate: 19%

edited 01 Sep '13, 05:57

ASA have "xml path" To solve more than one column

(01 Sep '13, 07:07) mfkpie8

Comment Text Removed

ELECT distinct list( DISTINCT trans_no+'+"'+REmark order by Trans_no,';' ) as newRE FROM #temp_1 where remark is not null and remark <> ' '

thanks

(03 Sep '13, 02:27) mfkpie8

flat view

Hm, I still do not understand what exact result set do try to get?

Do you want to avoid duplicates (here FH001 and FH002) - then you can simply use "SELECT DISTINCT trans_no, remark FROM #temp_1".

In order to concatenate values from several columns/rows to one column/row, you can use the LIST aggregate, as Justin has suggested, and LIST can use DISTINCT as well, such as

SELECT LIST(DISTINCT trans_no || '+"' || remark || '"+"', '' ORDER BY trans_no)
FROM #temp_1

Note, the ", ''" is used as the desired delimiter, here an empty one. That will lead to a final "+" in the output, as your result sample seems to contain.

I still don't understand the hint to XML pathes here...

permanent link

answered 02 Sep '13, 11:34

Volker Barth
39.3k●354●538●808
accept rate: 33%

ELECT distinct list( DISTINCT trans_no+'+"'+REmark order by Trans_no,';' ) as newRE FROM #temp_1 where remark is not null and remark <> ' '

YES thanks FRD can i get you IM !

(03 Sep '13, 02:27) mfkpie8

Replies hidden

So that does solve your problem? (I still don't understand truly...)

Two hints to your statement:

The first DISTINCT would be unnecessary, as LIST is an aggregate function (like SUM) and will only return one row, unless a GROUP BY clause is added, so a general SELECT DISTINCT is not needed.
LIST(... ORDER BY Trans_no, ';') will not use ';' as a delimiter, in contrast, it will order the entries by a) Trans_no and b) ';'. If you want to specify the delimiter, it must be specified BEFORE the ORDER BY clause, see my sample.

(03 Sep '13, 03:56) Volker Barth

flat view

I'm afraid your question is quite ambiguous. If you're looking to transpose your rows into columns, stay away from union and rather consider multiple inner select statements :

select (select remark || transaction from XM001 where trans_no='FH001') as FH001, (select remark || transaction from XM001 where trans_no='FH002') as FH002,...

I think... But i'm not sure that's exactly what you're looking for.

permanent link

answered 01 Sep '13, 05:49

Liam
361●9●11●18
accept rate: 0%

ASA have "xml path" To solve more than one column

(01 Sep '13, 07:08) mfkpie8

ELECT distinct list( DISTINCT trans_no+'+"'+REmark order by Trans_no,';' ) as newRE FROM #temp_1 where remark is not null and remark <> ' '

(03 Sep '13, 02:27) mfkpie8

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

column ×10

question asked: 01 Sep '13, 04:17

question was seen: 2,848 times

last updated: 03 Sep '13, 03:57

SAP SQL Anywhere

how to More than one column line joining together thanks

Follow this question

Related questions