I am attempting to concatenate multirow values into single row...I have achieved a close proximity but realize that the query below misses the first row every time...am I missing something obvious here? I have never used CTE before so I'm trying hard to understand the concepts behind it. Thanks in advance.
WITH CTE ( AnswerResponseRefId, QuestionComponentList, QuestionComponentName,
templevel )
AS (
SELECT
tao1.AnswerResponseRefId,
CAST( '' AS VARCHAR(MAX) ) AS QuestionComponentList,
CAST( '' AS VARCHAR(69) ) AS QuestionComponentName,
0 AS templevel
FROM
TargetAnswersOrdered tao1
WHERE tao1.QuestionRefId = 'B6944310-96FA-401C-86FE-A7BEA1D7B2B0'
and tao1.TargetRefId = '5E013FC7-5AC9-45E4-A4D7-000F3C241599'
GROUP BY tao1.AnswerResponseRefId
UNION ALL
SELECT
qc.AnswerResponseRefId,
CAST( c.QuestionComponentList +
CAST(c.QuestionComponentName AS VARCHAR(64)) AS VARCHAR(MAX) ),
CAST( qc.QuestionComponentRefId AS VARCHAR(64)) + '*V' +
CAST( qc.QuestionComponentVersion AS VARCHAR(2)) + '*',
templevel + 1
FROM CTE c
INNER JOIN
TargetAnswersOrdered qc
ON
c.AnswerResponseRefId = qc.AnswerResponseRefId
WHERE
CAST( qc.QuestionComponentRefId AS VARCHAR(64)) + '*V' +
CAST( qc.QuestionComponentVersion AS VARCHAR(2)) + '*'
> c.QuestionComponentName
)
SELECT
AnswerResponseRefId,
QuestionComponentList
FROM ( SELECT
AnswerResponseRefId,
QuestionComponentList,
RANK() OVER ( PARTITION BY
AnswerResponseRefId
ORDER BY templevel DESC )FROM CTE )
D ( AnswerResponseRefId, QuestionComponentList, rank )
WHERE rank = 1 ;