I have the following data sample below.
User Levl Clan Skills WpnCode
Sam 100 Daredevil Beginner
Sam 100 Daredevil Archery Archblst
Sam 100 Daredevil Archery Archaccy
Sam 100 Daredevil Powershot Cannon
Sam 100 Daredevil Powershot Rifle
Sam 125 Bluesy Beginner Rod
Sam 125 Bluesy Archery Greenarch
Sam 125 Bluesy Archery Bluearch
Sam 125 Bluesy Powershot Torch
Sam 125 Bluesy Powershot Knife
Sam 150 Stargaze Beginner Stick
Sam 150 Stargaze Powershot Magnum
Sam 150 Stargaze Powershot Pistol
UPDATE: Sorry, the sample data is not one table, here's the query to generate the sample data: 4/6: Figured out what's wrong with my pl/sql so just fixed it instead - thanks to both who have responded +1! Picking little foot since it made me think of other ways
select b.username User,
b.Level Levl,
b.Club Clan,
a.Skills,
a.Weapons WpnCode
from detl a,prof b
where a.usercode = b.username
and a.tagcde = b.tagid
and b.username = '&&User'; --Sam
Desired outputs:
varProf = Daredevil-Archblst,ArchAccy[Cannon,Rifle]
varProf2 = Bluesy-Greenarch,Bluearch[Torch,Knife],Stargaze[Magnum,Pistol]
First attempt for returning only varProf:
DECLARE
varProf VARCHAR2(500) := '';
BEGIN
SELECT
LISTAGG(
CASE
WHEN PSWpn = 'Beginner' THEN Clan
ELSE Clan || '[' || WpnList || ']'
END,
'-'
) WITHIN GROUP (ORDER BY Levl) INTO varProf
FROM (
SELECT = Clan, PSWpn, Levl,
LISTAGG(WpnCode, ',') WITHIN GROUP (ORDER BY WpnCode) AS WpnList
FROM (
SELECT
Club AS Clan,
CASE WHEN Skills = 'Powershot' THEN 'Powershot' ELSE 'Beginner' END AS PSWpn,
Level AS Levl,
Weapons AS WpnCode
FROM
-- my joins are here
WHERE
-- my conditions and prompts are here
)
GROUP BY Clan, PSWpn, Levl
);
DBMS_OUTPUT.PUT_LINE('varProf = ' ||varProf);
END;
It wasn't right since it appending (-) the Clan instead of the Archery Skill.
So I tried using another strategy by just looping the records:
DECLARE
varProf VARCHAR2(500) := '';
varProf2 VARCHAR2(500) := '';
BEGIN
FOR rec IN (
SELECT Club AS Clan, Weapons AS WpnCode, Level AS Levl, Skills
FROM
-- my tables
WHERE
-- my conditions
) LOOP
IF rec.Levl = 1 THEN
IF varProf IS NULL OR varProf = '' THEN
varProf := rec.Clan || '-' || rec.WpnCode;
ELSE
varProf := varProf || ',' || rec.Clan || '-' || rec.WpnCode;
END IF;
ELSE
IF varProf2 IS NULL OR varProf2 = '' THEN
varProf2 := rec.Clan || '-' || rec.WpnCode;
ELSE
varProf2 := varProf2 || ', ' || rec.Clan || '-' || rec.WpnCode;
END IF;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('varProf = ' || varProf);
DBMS_OUTPUT.PUT_LINE('varProf2 = ' || varProf2);
END;
While it did append the Skills, I did not include the listagg for the WpnCode... is there a better strategy to have the desired outputs that I want?
Basically the sample data is for one user and his profiles (I have the condition to prompt for the username in the where clause). But anyway, I want to return their 100 level Clan name. Followed by a "-" to append their Archery Weapons (separated by "," if multiple) which is identified by Skills = Archery. And finally append an enclosed [ ] which inside is the list of Powershot Weapons/WpnCode, separated by comma if multiple WpnCodes, and this is identified by Skills = Powershot.
Then, I also want to display Clan name(s), that are not level 100 (Levl <> 100). Using the same logic above, but the major difference is that they can have multiple over 100lv Clans, which I want them distinctly listed out as well (no dupes), separated by comma.
Here's the desired outputs once again:
varProf = Daredevil-Archblst,ArchAccy[Cannon,Rifle]
varProf2 = Bluesy-Greenarch,Bluearch[Torch,Knife],Stargaze[Magnum,Pistol]
Does it have to be PL/SQL? If so, split the following query in two separate SELECTs.
SQL> select a.clan ||'-'||
2 listagg(distinct b.wpncode, ',') ||'[' ||
3 listagg(distinct c.wpncode, ',') ||']' as result
4 from test a left join test b on a.clan = b.clan and b.skills = 'Archery'
5 left join test c on c.clan = a.clan and c.skills = 'Powershot'
6 where a.clan = '&&par_clan'
7 and a.skills = 'Beginner'
8 group by a.clan
9 union all
10 select listagg(result, ',')
11 from (
12 -- same query as above, with minor changes in WHERE clause
13 select a.clan ||'-'||
14 listagg(distinct b.wpncode, ',') ||'[' ||
15 listagg(distinct c.wpncode, ',') ||']' as result
16 from test a left join test b on a.clan = b.clan and b.skills = 'Archery'
17 left join test c on c.clan = a.clan and c.skills = 'Powershot'
18 where a.clan <> '&&par_clan'
19 and a.skills <> 'Beginner'
20 group by a.clan
21 -- end of same query
22 );
Enter value for par_clan: Daredevil
RESULT
--------------------------------------------------------------------------------
Daredevil-Archaccy,Archblst[Cannon,Rifle]
Bluesy-Bluearch,Greenarch[Knife,Torch],Stargaze-[Magnum,Pistol]
SQL>