Forum Stats

  • 3,759,245 Users
  • 2,251,515 Discussions
  • 7,870,550 Comments

Discussions

Wrap Column values into a single cell with <DIV>

User_QDHXF
User_QDHXF Member Posts: 20 Green Ribbon
edited Sep 17, 2021 2:16PM in SQL & PL/SQL

Hello,


Create Table T1_TEMP(ID number, YEAR number, CASES varchar2(10), count number);


insert into T1_TEMP values (1232, 2015,'14-A786',7);

insert into T1_TEMP values (1232, 2016,'13-5767',7);

insert into T1_TEMP values (1232, 2016,'12-G674',7);

insert into T1_TEMP values (1232, 2017,'17-6YG4',7);

insert into T1_TEMP values (1232, 2018,'11-07A5',7);

insert into T1_TEMP values (1232, 2018,'03-1A55',7);

insert into T1_TEMP values (1232, 2018,'03-17A1',7);

insert into T1_TEMP values (1232, 2019,'02-519A',7);

insert into T1_TEMP values (1232, 2020,'07-A594',7);

insert into T1_TEMP values (1232, 2020,'09-A976',7);

insert into T1_TEMP values (1232, 2020,'10-781I',7);

I have data in this format:



I need to combine the fields YEAR and CASES into one column and have a <DIV> tag for each year. My expected data format is:

Is that doable? I am looking to get just one row for each ID. Greatly appreciate any help.


Thank You.

Best Answer

  • Jan Gorkow
    Jan Gorkow Member Posts: 133 Gold Badge
    edited Sep 17, 2021 2:30PM Accepted Answer

    Hi @User_QDHXF ,

    check this out:

    WITH
       tmp
       AS
          (  SELECT t1_temp.id,
                    t1_temp.year,
                    LISTAGG (t1_temp.cases, ',')    AS cases,
                    t1_temp.COUNT
               FROM t1_temp
           GROUP BY t1_temp.id, t1_temp.year, t1_temp.COUNT)
      SELECT tmp.id,
             LISTAGG (
                '<div>' || TO_CHAR (tmp.year) || ': ' || tmp.cases || '</div>',
                CHR (10))
             WITHIN GROUP (ORDER BY tmp.year ASC)   AS new_col,
             tmp.COUNT
        FROM tmp
    GROUP BY tmp.id, tmp.COUNT
    ORDER BY tmp.id ASC, tmp.COUNT ASC;
    

    Best regards,

    Jan

    User_QDHXF

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 485 Bronze Trophy

    SQL Language reference/listagg (nested)

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,063 Red Diamond

    HI, @User_QDHXF

    Whenever you have a question, please post CREATE TABLE and INSERT statements for a little sample data, so the people who want to help you can re-create the problem and test their ideas. Also post the exact results you want from that data, and explain why you want those results from that data. Always post your complete Oracle version (e.g. 18.4.0.0.0).

    You did post the desired output, but it's unclear if you want 7 rows of output or only 1.

    The aggregate LISTAGG function may help in this problem.

  • User_QDHXF
    User_QDHXF Member Posts: 20 Green Ribbon

    @User_H3J7U : Tried that approach. But haven't got much far with it. The specific format of <DIV> tags is what's throwing me off.

    The format must be :

    <div>

    <div>YEAR1: CASES_FOR_YEAR1</div>

    <div>YEAR2: CASES_FOR_YEAR2</div>

    <div>YEAR3: CASES_FOR_YEAR3</div>

    </div>

  • Jan Gorkow
    Jan Gorkow Member Posts: 133 Gold Badge
    edited Sep 17, 2021 2:30PM Accepted Answer

    Hi @User_QDHXF ,

    check this out:

    WITH
       tmp
       AS
          (  SELECT t1_temp.id,
                    t1_temp.year,
                    LISTAGG (t1_temp.cases, ',')    AS cases,
                    t1_temp.COUNT
               FROM t1_temp
           GROUP BY t1_temp.id, t1_temp.year, t1_temp.COUNT)
      SELECT tmp.id,
             LISTAGG (
                '<div>' || TO_CHAR (tmp.year) || ': ' || tmp.cases || '</div>',
                CHR (10))
             WITHIN GROUP (ORDER BY tmp.year ASC)   AS new_col,
             tmp.COUNT
        FROM tmp
    GROUP BY tmp.id, tmp.COUNT
    ORDER BY tmp.id ASC, tmp.COUNT ASC;
    

    Best regards,

    Jan

    User_QDHXF
  • User_QDHXF
    User_QDHXF Member Posts: 20 Green Ribbon

    @Frank Kulash Thank you for the suggestion. Updated the question.

  • User_QDHXF
    User_QDHXF Member Posts: 20 Green Ribbon

    @Jan Gorkow Much appreciate the help. Thank you.

    Jan Gorkow
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,063 Red Diamond

    Hi, @User_QDHXF

    Updated the question.

    Please don't change your messages after you post them; it makes the thread hard to follow, and your changes easy to miss. Post all additions and corrections in a new message at the end of the thread.

    User_QDHXF
  • mathguy
    mathguy Member Posts: 10,081 Blue Diamond

    If you are manipulating XML data (creating it, in this thread), it is better to use XML functions as much as possible. They will take care of various things for you - for example escaping special characters (which the "accepted answer" does not do).

    Something like this (leaving out the COUNT column, which doesn't make sense as you posted it, but doesn't seem to be your immediate concern):

    select id, xmlagg(y order by year_) as new_col
    from   (
             select id, year_,
                    xmlelement("div", to_char(year_) || ': ' ||
                               listagg(cases, ',') within group (order by cases)) y
             from   t1_temp
             group  by id, year_
           )
    group  by id
    ;
    


    Note that this will not show each tag on a separate line - but that is not a requirement for valid XML.