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
result (Merger and took out the only "trans_no"): FH001+"test1"+FH002+"test2"+FH003+"test3"+FH004+"test4"+ |
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? 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
|
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... 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:
(03 Sep '13, 03:56)
Volker Barth
|
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. 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
|
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
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.
i modify my question! see my question please!thank
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...