cancel
Showing results for 
Search instead for 
Did you mean: 

how to More than one column line joining together thanks

ximen
Participant
3,388

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"+

Accepted Solutions (0)

Answers (3)

Answers (3)

VolkerBarth
Contributor

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
Participant
0 Kudos

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
Contributor
0 Kudos

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
Participant

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
Participant
0 Kudos

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

ximen
Participant
0 Kudos

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
0 Kudos

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
Participant
0 Kudos

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

ximen
Participant
0 Kudos

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