You can translate the question and the replies:

Create new records per column value

I have a table like this: | Name | job_1 | job_2 | job_n | | -------- | -------- | ------- | ------- | | Paul | Architect | null | null | | Eva | Nurse | MD | null | | John | Lawyer | Seller | Writer | I need to create a new table and the information must be something like: (a row per job) | Name | job | | -------- | ---- | | Paul | Architect | | Eva | Nurse | | Eva | MD | | John | Lawyer | | John | Seller | | John | Writer | I tried to do that adding a new field in the new table (job) with nested case, but if the person has more than 1 job take only the first. How can I do that?
24-05-2019 08:29:35 -0400

1 Answer

Hi, To get a row for a single job you can create a union with a branch for every column. In this case, job_1, job_2, and job_3 will be transformed into the column jobs in a new view. In order to do that, in this case, the easiest way is to create the view manually using VQL CREATE VIEW dv_multiple_jobs AS SELECT name, job_1 as jobs FROM bv_multiple_jobs WHERE job_1 <> '' UNION SELECT name, job_2 as jobs FROM bv_multiple_jobs WHERE job_2 <> '' UNION SELECT name, job_3 as jobs FROM bv_multiple_jobs WHERE job_3 <> '' You can also do this graphically, by creating a projection view for every branch and then create the view: Create a projection view for every branch maintaining Name column and one of the job columns for each view. Every view will have a condition for empty values for the same column that you are projecting on the view. Create a Union View using those projections and include an association for the JobX fields Execute the view and you will get the expected results. Hope this helps!
Denodo Team
28-05-2019 20:21:09 -0400
You must sign in to add an answer. If you do not have an account, you can register here