SELECT SYS_CONNECT_BY_PATH (RQ_REQ_NAME, '\') As "Requirement Path"
, RQ_REQ_ID As "Requirement ID"
, RQ_REQ_NAME As "Requirement Name"
CONNECT BY PRIOR RQ_REQ_ID = RQ_FATHER_ID
START WITH RQ_FATHER_ID = -1
NB: ORACLE ONLY. SYS_CONNECT_BY_PATH doesn't have a SQL SERVER equivalent.
"SYS_CONNECT_BY_PATH is valid only in hierarchical queries. It returns the path of a column value from root to node, with column values separated by char for each row returned by CONNECT BY condition."
In the above query:
Line 1: create a hierarchical string of RQ_REQ_NAME's, separated with a backslash
Line 5: for each requirement, trace back up the tree. The preceding requirement is the one with req_id = father_id of current requirement
Line 6: we're building the string backwards (connect by prior), we need to stop when we find a requirement with father_id = -1 (requirements root)
That's my understanding of it, and it seems to work, but I'm not a DBA.