January 2nd, 2014, 09:53 AM
Script to CTE
I have a table which has 2 columns and many rows (below is a example : original table has around ~2k lines in it ).
2 columns are
I need to have a script which can give me output which will have directreport till 2 level deep ( Ignore anything which is more than 2 levels deep )
manager, directreport, directreport1, directreport2, directmanager1, directmanager2
so for abovve the output should be
peter john null null null null
peter joe null null null null
peter sam null null null null
peter null domc null joe null
peter null hans null joe null
peter null null harry null domc
boyd jill null null null null
boyd rav null null null null
boyd null jeen null rav null
raj venkat null null null null
raj surku null null null null
joe domc null null null null
joe hans null null null null
joe null harry null domc null
rav jean null null null null
surku pam null null null null
domc harry null null null null
January 2nd, 2014, 11:02 AM
In what way are 'Common Table Expressions' involved?
Does this data come from a database? If so, is there more data available than that which you provided? Because those names are not a good way to determine uniqueness. It is likely that two instances of John will not refer to the same person.
January 2nd, 2014, 11:41 AM
yes the data is coming from a database.
Regarding the names actual table uses a unique identity and there will not be duplication of names.
January 5th, 2014, 09:20 AM
Sorry for the late response.
Depending on your database and table, you may find the task much easier using a select statement than trying to do the job externally in perl. Example here at wikipedia:
Hierarchical and recursive queries in SQL
As you can see from the first example, selecting the level makes formatting the output much easier.
But I believe the task could be accomplished in perl alone if uniqueness of entries was guaranteed (say by selecting the row_id rather than just the name. It would probably involve looping over the data more than once to locate the level in the hierarchy however.
January 5th, 2014, 07:25 PM
Thank you for your reply.
I will go through the link. Seems to be complicated using sql query as well.
I am new to scripting. If you have any sample code would be great.
January 6th, 2014, 04:33 PM
If you want to do it in pure Perl, rather than SQL, then using two hashes should get you there.
One simple hash to go up the hierarchy: key is the report and value the manager (assuming any report has only one direct manager)
One hash of arrays to go down the hierarchy; key is the manager and the array contains all direct reports.
Populate the two hashes when you read the input.
Then you just have to go through the hashes to prepare your output.