List tests including path

Question ID: 105087
4
0

I am looking for a way to list all the tests including the tree hierarchy using a SQL solution not using the API.

Marked as spam
Posted by (Questions: 70, Answers: 111)
Asked on July 31, 2013 8:10 pm
5 views
Answers (2)
4
Private answer

Have you seen this post? http://eyeontesting.com/questions/1891/need-an-excel-report-that-includes-path-and-steps.html

Marked as spam
Posted by (Questions: 5, Answers: 1)
Answered on July 31, 2013 8:12 pm
That is a good one for sure. But I am looking for a pure SQL solution for SQL Server.
( at July 31, 2013 8:14 pm)
4
Private answer

Try this.

WITH LIST_CTE AS
(
SELECT AL.AL_ITEM_ID
,AL.AL_FATHER_ID
,CAST(AL.AL_DESCRIPTION AS VARCHAR(MAX)) ''AL_DESCRIPTION''
FROM TD.ALL_LISTS AL
UNION ALL
SELECT AL.AL_ITEM_ID
,AL.AL_FATHER_ID
,LIST_CTE.AL_DESCRIPTION + '' + AL.AL_DESCRIPTION
FROM LIST_CTE
JOIN TD.ALL_LISTS AL
ON LIST_CTE.AL_ITEM_ID = AL.AL_FATHER_ID
)
SELECT
LIST_CTE.AL_DESCRIPTION
, TD.TEST.TS_NAME
, TD.TEST.TS_TEST_ID
, TD.TEST.TS_RESPONSIBLE
, TD.TEST.TS_TYPE
, TD.TEST.TS_EXEC_STATUS
FROM
LIST_CTE
JOIN TD.TEST ON TD.TEST.TS_SUBJECT = LIST_CTE.AL_ITEM_ID
WHERE
LIST_CTE.AL_DESCRIPTION LIKE 'SUBJECT%'
ORDER
BY LIST_CTE.AL_DESCRIPTION, TS_NAME

Marked as spam
Posted by (Questions: 0, Answers: 613)
Answered on July 31, 2013 8:16 pm
Thanks for the help. Excellent solution....NO API Yeah!!!!
( at July 31, 2013 8:24 pm)