Problem in T-sql

I have a table called results and the data looks like:

Response_ID  order    [part label]             [Answer text]     [Answer label]
124587       6        It was not clear         NULL               Yes
124587       6        Did not Understand      Null                Yes
124589       6        Other (Please specify):  Not enough         Yes 
124563       1        NULL                     Satisfied?         Yes
124583       11       Not frequent             NULL               Yes  
125687       2        NULL                     Resolved?          NO

I want Output as:

Response_ID           [Part label]
124587                It was not clear,Did not Understand 
124589           开发者_Python百科     Not enough         
124563                Yes
124583                Not frequent
125687                NO

The logic is whenever Order is 6 or 11 then I need to display the [Part Label] if the [Part Label] has multiple value for one Response_ID then I need to concatenate them but when the value of [Part Label] is Other (Please specify): then I need to use value from Answer Text column and if the order is not in 6,11 then I need to display the value from Answer label

---

I had not seen the part about the order. I hope it works now.

DECLARE @t TABLE (Response_ID INT,[order] INT, [part label] VARCHAR(25), [Answer text] VARCHAR(15), [Answer label] VARCHAR(3))

INSERT @t VALUES (124587,6 , 'It was not clear'       , NULL        , 'Yes')
INSERT @t VALUES (124587,6 , 'Did not Understand'    , Null        , 'Yes' )
INSERT @t VALUES (124589,6 , 'Other (Please specify):','Not enough' , 'Yes')
INSERT @t VALUES (124563,1 , NULL                     ,'Satisfied?' , 'Yes')
INSERT @t VALUES (124583,11, 'Not frequent'           , NULL        , 'Yes') 
INSERT @t VALUES (125687,2 , NULL                     ,'Resolved?'  , 'NO' )

SET ARITHABORT ON

;WITH x AS   ( 
SELECT CASE WHEN [order] = 11 THEN 6 ELSE [order] END [order], response_id, COALESCE(CASE WHEN [part label] = 'Other (Please specify):' THEN [Answer text] ELSE [part label] end ,[Answer label]) [Part label] 
FROM @t
) 
SELECT response_id, STUFF(( 
        SELECT ',' + [Part label] 
        FROM x t1 
        WHERE t1.response_id = x.response_id and t1.[order] = x.[order]
        for xml path(''), type 
    ).value('.', 'varchar(max)'), 1, 1, '') [Part label] FROM x
GROUP BY response_id, [order]

Result:

response_id Part label
----------- -------------------------------------
124563      Yes
124583      Not frequent
124587      It was not clear,Did not Undersstand
124589      Not enough
125687      NO

---

This used to be achieved through a little-known angle involving ISNULL(). These days, people do it with "FOR XML PATH". Note that this is not supported, but it will work for you right now.

Concatenating Row Values in T-SQL

---

Too bad, there is no simple solution because Sql Server cannot concatenate result set rows. Look at these questions:

Concatenate many rows into a single text string?

How to create a SQL Server function to "join" multiple rows from a subquery into a single delimited field?

---

Check out the "The blackbox XML methods" section of the following article. I believe it will meet your needs.

https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/