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

asked 01 Sep '13, 04:17

mfkpie8's gravatar image

mfkpie8
86424751
accept rate: 10%

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
1

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
1

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

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%20Willey's gravatar image

Justin Willey
6.6k106136205
accept rate: 21%

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

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%20Barth's gravatar image

Volker Barth
30.0k294447654
accept rate: 32%

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

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's gravatar image

Liam
36191118
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
Your answer
toggle preview

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

Question tags:

×9

question asked: 01 Sep '13, 04:17

question was seen: 1,149 times

last updated: 03 Sep '13, 03:57