how to More than one column line joining together thanks

[ximen]

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 'test1' as remark, 'FH001' as trans_no union all

select 'test2' as remark, 'FH002' 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"+

[justin_willey]

I think you've just repeated your original post - can you try to explain more clearly what you are trying to do?

[ximen]

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

[ximen]

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

[Breck_Carter]

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.

[ximen]

i modify my question! see my question please!thank

[VolkerBarth]

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...

[VolkerBarth]

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...

[ximen]

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 !

[VolkerBarth]

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.

[justin_willey]

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?

[ximen]

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

[ximen]

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

thanks

[Former Member]

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.

[ximen]

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

[ximen]

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