How to concatenate and transverse query in SQL Server?

Question

I would like to change the output of this query, into the second snippet with less rows:

id   row_id   element_id    container_id   text              answer
--------------------------------------------------------------------
1      1        10          100            Question1:         Yes
1      1        10          100            Did you check...   Yes
1      2        10          100            Question2:         Yes
1      2        10          100            Did you verify...  Yes

Desired output:

id  row_id element_id container_id   Question                         answer
----------------------------------------------------------------------------
1   1       10          100          Question1: Did you check..        Yes
1   2       10          100          Question2: Did you verify...      Yes

Therefore making the table shorter. I know this can be done in MySQL with a group_concat function, but I was not able to apply it correctly to my need.

Any help would be great.

Thank you

As you present the problem here is your “stuck” point: How do you know that “Did you verify…” does not go to “Question1:”? We see it after Question 2 in the natural order but how would you make a query know that the second answer “Did you verify…” goes to Question2: and not Question1: . This problem also extends to the answer column. Solution- IF the text values “Question1:” and “Did you check…” exist in another child table or lookup table you probably can get to what you want. — Sql Surfer, Mar 02 '17 at 00:51
Hi @SqlSurfer, thanks for pointing that out. I had forgotten to include the distinguishing column. I updated the code to show what my case is. — rafacardosoc, Mar 02 '17 at 01:38
http://stackoverflow.com/questions/10553198/merge-multiple-rows-into-a-single-row/10553404#10553404 — SQLMason, Mar 02 '17 at 01:41
Hi @DanAndrews, does that mean the resolution must be using a stored procedure? Sorry for the newbie question. Thank you — rafacardosoc, Mar 02 '17 at 01:47

score 1 · Answer 1 · edited May 23 '17 at 11:46

See my answer to a very similar (duplicate?) question here: Merge multiple rows into a single row

SELECT  [id],  
        [row_id], 
        [element_id], 
        [container_id],
        (
          SELECT    STUFF((
                            SELECT  [text]
                            FROM    @table
                            WHERE   [ID] = tbl.[ID]
                                    AND [row_ID] = tbl.[row_ID]
                                    AND [element_id] = tbl.[element_id]
                                    AND [container_id] = tbl.[container_id]
                                    and [answer] = tbl.[answer]
                            GROUP BY [text]
                          FOR
                            XML PATH('')
                          ), 0, 0, '') -- you may have to change to 1,0 or 1,1
        ) Question,
        [answer]
FROM    @table tbl
GROUP BY [id],  
        [row_id], 
        [element_id], 
        [container_id],
        [answer]

score 1 · Accepted Answer · answered Mar 02 '17 at 01:53

This literally answers the question - I would recommend a more formal fix in the database table structures or modifications to the insert and update statements that fill the data. (Test against unanswered questions, may need a left join)

 SELECT q.id
       , q.row_id 
       , q.container_id 
       , q.Text 
       , q.answer 
       , a.answer
       , a.Text 
       , q.Text + ' ' + a.Text as SqlFor300Alex 
    FROM tblQandA q 
   INNER JOIN tblQandA a on q.id = a.id 
                        and q.row_id = a.row_id 
                        and q.element_id = a.element_id 
                        and q.container_id = a.container_id 
                        and a.Text NOT Like('Question%') 
    WHERE q.Text Like('Question%')

I like yours better because you're sure to get Question before the text. IDK if there would always be 2 and if there were, would he want two records for it? I believe "probably" — SQLMason, Mar 02 '17 at 01:58

score 0 · Answer 3 · answered Mar 02 '17 at 05:00

Try this,

declare @t table(id int,row_id int,element_id int,container_id int,   texts varchar(500)
, answer varchar(500),rowtype int)
insert into @t VALUES
(1,1,10,100,'Question1:','Yes',1)
,(1,1,10,100,'Did you check...','Yes',2)
,(1,2,10,100,'Question2: ','Yes',1)
,(1,2,10,100,'Did you verify..','Yes',2)

select id,row_id,element_id,container_id,texts
+' '+(select top 1 texts from @t b 
where b.row_id=a.row_id and b.rowtype=2 ) 
,answer
from @t a
where rowtype=1

;With CTE  as
(
 select  id,row_id,element_id,container_id,texts,answer
 ,case when CHARINDEX('Question',texts)>0 
 then 1 else 2 end Rowtype from @t
)
select id,row_id,element_id,container_id,texts
+' '+(select top 1 texts from cte b 
where b.row_id=a.row_id and b.rowtype=2 ) 
,answer
from cte a
where rowtype=1

How to concatenate and transverse query in SQL Server?

3 Answers3