Concatenate? 1

[klunde]

I have this sql code

select source_name, round(sysdate-sup_date) as nday, count(article_id) as arts
from articles_contents_xml
where round(sysdate-sup_date) <=14
group by 
source_name, round(sysdate-sup_date)"
order by source_name, nday

which gives me this list:

Source_name	nday	arts
Adresseavisen	1	17
Adresseavisen	3	38
Adresseavisen	4	35
Adresseavisen	5	29
Adresseavisen	6	33
Aftenposten 	1	14
Aftenposten 	4	23

I would like to transform this into a new list like this:

Source_name	Day1	day2	Day3	Day4	Day5	Day6
Adresseavisen	17	-	38	35	29	33
Aftenposten 	14	-	-	23	-	-

Can I use concat() with my Oracle 10 and change the SQL to accomplish this?

</Morten>

 

[Dagon]

I think the function you are looking for is called COLLECT rather than CONCAT. Concat is just another name for the || function which joins strings together.

You could do it using something like:

create table testdat (source_name varchar2(30), nday number, arts number)

insert into testdat values ('Adresseavisen',    1,    17);
insert into testdat values ('Adresseavisen',   3,    38);
insert into testdat values ('Adresseavisen',    4,    35);
insert into testdat values ('Adresseavisen',    5,    29);
insert into testdat values ('Adresseavisen',    6,    33);
insert into testdat values ('Aftenposten',     1,   14);
insert into testdat values ('Aftenposten',     4,    23);

select source_name,
case when instr(long_string, '1:') <> 0 then substr(long_string, instr(long_string, '1:')+2,instr(long_string, ',' ,instr(long_string, '1:')) - instr(long_string, '1:')-2) end as day1,
case when instr(long_string, '2:') <> 0 then substr(long_string, instr(long_string, '2:')+2,instr(long_string, ',' ,instr(long_string, '2:')) - instr(long_string, '2:')-2) end as day2,
case when instr(long_string, '3:') <> 0 then substr(long_string, instr(long_string, '3:')+2,instr(long_string, ',' ,instr(long_string, '3:')) - instr(long_string, '3:')-2) end as day3,
case when instr(long_string, '4:') <> 0 then substr(long_string, instr(long_string, '4:')+2,instr(long_string, ',' ,instr(long_string, '4:')) - instr(long_string, '4:')-2) end as day4,
case when instr(long_string, '5:') <> 0 then substr(long_string, instr(long_string, '5:')+2,instr(long_string, ',' ,instr(long_string, '5:')) - instr(long_string, '5:')-2) end as day5,
case when instr(long_string, '6:') <> 0 then substr(long_string, instr(long_string, '6:')+2,instr(long_string, ',' ,instr(long_string, '6:')) - instr(long_string, '6:')-2) end as day6
from
(select source_name, tab_to_string(CAST(collect(nday||':'||arts) AS t_varchar2_tab)) as long_string
from testdat
group by source_name)

 

[Dagon]

I forgot to mention that you need to create a couple of database objects for that to work.

CREATE OR REPLACE TYPE t_varchar2_tab AS TABLE OF VARCHAR2(4000);
/

CREATE OR REPLACE FUNCTION tab_to_string (p_varchar2_tab  IN  t_varchar2_tab,
                                          p_delimiter     IN  VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS
  l_string     VARCHAR2(32767);
BEGIN
  FOR i IN p_varchar2_tab.FIRST .. p_varchar2_tab.LAST LOOP
    l_string := l_string || p_varchar2_tab(i) || p_delimiter;
  END LOOP;
  RETURN l_string;
END tab_to_string;
/

 

[SantaMufasa]

Morten,

There are many ways to resolve your need. Here is one of the ways:

select source_name
      ,substr(nvl(to_char(max(decode(nday,1,arts,null))),'-'),1,4) Day1
      ,substr(nvl(to_char(max(decode(nday,2,arts,null))),'-'),1,4) Day2
      ,substr(nvl(to_char(max(decode(nday,3,arts,null))),'-'),1,4) Day3
      ,substr(nvl(to_char(max(decode(nday,4,arts,null))),'-'),1,4) Day4
      ,substr(nvl(to_char(max(decode(nday,5,arts,null))),'-'),1,4) Day5
      ,substr(nvl(to_char(max(decode(nday,6,arts,null))),'-'),1,4) Day6
  from (select source_name, round(sysdate-sup_date) as nday, count(article_id) as arts
          from articles_contents_xml
         where round(sysdate-sup_date) <=14
         group by source_name, round(sysdate-sup_date)
         order by source_name, nday)
 group by source_name
/

SOURCE_NAME   DAY1 DAY2 DAY3 DAY4 DAY5 DAY6
------------- ---- ---- ---- ---- ---- ----
Adresseavisen 17   -    38   35   29   33
Aftenposten   14   -    -    23   -    -

Let us know if this satisfactorily resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[klunde]

Thank you both for very good help. I went for the second method since I'm writing up this page in an asp page and therefor didn't want to create new database objects.

A small? follow-up question. Would it be possible to change the sql code to allow n-number of days?

</Morten>

 

[SantaMufasa]

Would it be possible to change the sql code to allow n-number of days?

Yes, one method to achieve your result is to use a technique called "SQL Writing SQL". In the sample code, below, I use SQL*Plus and SQL commands to write the necessary code to accommodate "n-number of days". First, let's look at some sample data that extends the data beyond your original 6 rows:

select source_name, round(sysdate-sup_date) as nday, count(article_id) as arts
from articles_contents_xml
where round(sysdate-sup_date) <=14
group by 
source_name, round(sysdate-sup_date)"
order by source_name, nday;

SOURCE_NAME         NDAY       ARTS
------------- ---------- ----------
Adresseavisen          1         17
Adresseavisen          3         38
Adresseavisen          4         35
Adresseavisen          5         29
Adresseavisen          6         33
Adresseavisen          8         43
Adresseavisen         10         53
Adresseavisen         12         63
Aftenposten            1         14
Aftenposten            4         23

Next, here is the code, which I saved into a script named "tt_507.sql", that accommodates n-number of days-worth of data:

set pagesize 0
set feedback off
set echo off
set termout off
col max_days new_value max_days
select max(nday) max_days
  from (select round(sysdate-sup_date) as nday
          from articles_contents_xml
         where round(sysdate-sup_date) <=14
         group by source_name, round(sysdate-sup_date));
spool temp.sql
set termout on
prompt Select source_name
select ',substr(nvl(to_char(max(decode(nday,'||cnt||',arts,null))),''-''),1,6) Day'||cnt
  from (select level cnt from dual connect by level <= &max_days);
prompt   from (select source_name, round(sysdate-sup_date) as nday, count(article_id) as arts
prompt           from articles_contents_xml
prompt          where round(sysdate-sup_date) <=14
prompt          group by source_name, round(sysdate-sup_date)
prompt          order by source_name, nday)
prompt  group by source_name
prompt /
spool off
set pagesize 35
@temp

Here are the results of running "tt_507.sql":

SQL> @tt_507
Select source_name
,substr(nvl(to_char(max(decode(nday,1,arts,null))),'-'),1,6) Day1
,substr(nvl(to_char(max(decode(nday,2,arts,null))),'-'),1,6) Day2
,substr(nvl(to_char(max(decode(nday,3,arts,null))),'-'),1,6) Day3
,substr(nvl(to_char(max(decode(nday,4,arts,null))),'-'),1,6) Day4
,substr(nvl(to_char(max(decode(nday,5,arts,null))),'-'),1,6) Day5
,substr(nvl(to_char(max(decode(nday,6,arts,null))),'-'),1,6) Day6
,substr(nvl(to_char(max(decode(nday,7,arts,null))),'-'),1,6) Day7
,substr(nvl(to_char(max(decode(nday,8,arts,null))),'-'),1,6) Day8
,substr(nvl(to_char(max(decode(nday,9,arts,null))),'-'),1,6) Day9
,substr(nvl(to_char(max(decode(nday,10,arts,null))),'-'),1,6) Day10
,substr(nvl(to_char(max(decode(nday,11,arts,null))),'-'),1,6) Day11
,substr(nvl(to_char(max(decode(nday,12,arts,null))),'-'),1,6) Day12
from (select source_name, round(sysdate-sup_date) as nday, count(article_id) as arts
from articles_contents_xml
where round(sysdate-sup_date) <=14
group by source_name, round(sysdate-sup_date)
order by source_name, nday)
group by source_name
/

SOURCE_NAME   DAY1   DAY2   DAY3   DAY4   DAY5   DAY6   DAY7   DAY8   DAY9   DAY10  DAY11  DAY12
------------- ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------
Adresseavisen 17     -      38     35     29     33     -      43     -      53     -      63
Aftenposten   14     -      -      23     -      -      -      -      -      -      -      -

There are some fairly complex uses of both SQL*Plus and of SQL, so let us know if you have follow-on questions regarding the code, above, and if the code satisfactorily resolves your need.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[klunde]

Thanks again!

one final small question. In the first example you have

,substr(nvl(to_char(max(decode(nday,1,arts,null))),'-'),1,4) Day1

while in the last example you have

,substr(nvl(to_char(max(decode(nday,1,arts,null))),'-'),1,6) Day1

Why 4 on the first and 6 on the second?

</Morten>

 

[SantaMufasa]

Why 4 on the first and 6 on the second?

In your first example, you wanted to accommodate column headings up to "Day6" (4 chrs.) in length. In your more recent specifications, you wanted "n-number of days", so I allowed for "Day999" (6 chrs.) of output.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[klunde]

ah... got it.

Thanks again for your excellent help!

</Morten>

 

[SantaMufasa]

My pleasure ! Good luck, Morten.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”