views:

21

answers:

1

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 ;
A: 

Solved..

using wrong ref's in

CAST( c.QuestionComponentList + CAST( qc.QuestionComponentRefId AS VARCHAR(64)) + 'V' + CAST( qc.QuestionComponentVersion AS VARCHAR(2)) + '' AS VARCHAR(MAX) ) AS QuestionComponentList,

Mike