Concatenate multiple rows in the same column of Oracle into a string using SQL.

Source: Internet
Author: User

Concatenate multiple rows of records in the same column in Oracle into a string [SQL] -- raw data -- a 111 -- B 222 -- a 333 -- a 444 -- B 555 -- final result -- a 111*333*444 SELECT L4.L _ TIME, MAX (SUBSTR (L4. group CONTENT, 2) final field value FROM (SELECT L3.L _ TIME, SYS_CONNECT_BY_PATH (L3.L _ CONTENT ,'*') AS group content from (SELECT L2.L _ TIME, L2.L _ CONTENT, L2.L _ TIME | L2. group id as group field plus number, l2.L _ TIME | (L2. group ID-1) AS upper-level group field plus number FROM (SELECT L1.L _ TIME -- group basis, l1.L _ CONTENT -- the values of different rows to be merged in the same column. The value is from logs L1 in the ROW_NUMBER () OVER (partition by L1.L _ time order by L1.L _ content asc) group) l2) L3 start with L3. The upper-level grouping field is numbered LIKE '% 0' connect by prior L3. The grouping field is numbered = L3. The upper-level grouping field is numbered) l4 WHERE L_TIME = '000000' group by L4.L _ TIME -- ROW_NUMBER () OVER (partition by a order by B desc) new column name -- group by a and sort by B within the GROUP, the value calculated by this function indicates the sequential number after sorting in each group (the sequential and unique in the group) -- SYS_CONNECT_BY_PATH function: the first parameter is a field in the form of a tree, the second parameter is the delimiter used to separate the parent and its child. -- connect by prior indicates the parent-child relationship. -- start with indicates the node that you want to START traversing.: drop table logs; create table logs (L_ID VARCHAR2 (32) not null, L_CONTENT VARCHAR2 (512), L_TIME VARCHAR2 (32), L_USER VARCHAR2 (32 ), primary key (L_ID); comment on table logs is 'Log table'; comment on column logs. rochelle id is 'Log id'; comment on column logs. rochelle content IS 'Log content'; comment on column logs. rochelle time IS 'time'; comment on column logs. rochelle user IS 'operator '; insert into logs (L_ID, L_CONTENT, L_TIME, L_USER) VALUES ('123456', 'black', '123456', '1234568 '); insert into logs (L_ID, L_CONTENT, L_TIME, L_USER) VALUES ('000000', 'white', '000000', '000000'); insert into logs (L_ID, L_CONTENT, rochelle time, Rochelle user) VALUES ('123', 'black and white ', '123', '123'); insert into logs (Rochelle ID, Rochelle content, L_TIME, Rochelle user) VALUES ('20140901', 'White la', '20160901', '20160901'); insert into logs (L_ID, L_CONTENT, L_TIME, L_USER) VALUES ('20160301 ', 'Hey, ', '123', '123 ');

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.