I tried using the Listagg function to aggregate string but this does not accept ranking and it lists "all the data"- I need only data for rank 1-5- Does Listagg function has a way to limit the results-
Do not know if I can list the Listagg functions
I tried using a simple case statement -attached data but I need to concatenate all the columns from 1 to 5 to have data in one row.
This is the logical SQL code
SELECT "Candidate Identification"."Candidate
Identifier" saw_0,
CASE WHEN RANK("Submission Work Experience
History"."Submission Experience Start Date")= 3 Then
"Submission Work Experience History"."Submission Experience Job
Title (Other)" END saw_1,
CASE WHEN RANK("Submission Work Experience
History"."Submission Experience Start Date")= 5 Then
"Submission Work Experience History"."Submission Experience Job
Title (Other)" END saw_2,
"Submission Work Experience
History"."Submission Experience Start Date" saw_3,
RANK("Submission Work Experience
History"."Submission Experience Start Date") saw_4,
"Submission Work Experience
History"."Submission Experience End Date" saw_5,
CASE WHEN RANK("Submission Work Experience
History"."Submission Experience Start Date")= 1 Then "Submission
Work Experience History"."Submission Experience Job Title
(Other)" END saw_6,
"Submission General Info"."Submission
Identifier" saw_7,
CASE WHEN RANK("Submission Work Experience
History"."Submission Experience Start Date")= 2 Then
"Submission Work Experience History"."Submission Experience Job
Title (Other)" END saw_8,
CASE WHEN RANK("Submission Work Experience
History"."Submission Experience Start Date")= 4 Then
"Submission Work Experience History"."Submission Experience Job
Title (Other)" END saw_9
FROM "Recruiting" WHERE ("Candidate
Identification"."Candidate Identifier" = 12345) AND
("Submission General Info"."Submission Identifier" = 126000) AND
(RANK("Submission Work Experience History"."Submission
Experience Start Date") < 6)
I need a One Column that lists all 5 positions in a string format.
Thanks for any ideas!