Problem in T-sql

Question

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                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

Is there a question in there somewhere? Are you looking to have us write your code for you? What have you tried? — JNK
– JNK, Commented Jul 7, 2011 at 17:43
@JNK I did refer stackoverflow.com/questions/6614048/error-in-case-statement But the query I wrote is not working — user556674
– user556674, Commented Jul 7, 2011 at 17:46

t-clausen.dk · Accepted Answer · 2011-07-07 21:00:48Z

3

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

edited Jul 7, 2011 at 21:00

answered Jul 7, 2011 at 18:09

t-clausen.dk

44.5k12 gold badges61 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user556674 Over a year ago

@ t-clause It works fine for the above sample data but it fails if one reponse_ID has different order values

t-clausen.dk Over a year ago

I tried to adjust my script to work for different orders sets

Pittsburgh DBA · Accepted Answer · 2011-07-07 17:50:57Z

1

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

answered Jul 7, 2011 at 17:50

Pittsburgh DBA

6,8042 gold badges44 silver badges71 bronze badges

Collectives™ on Stack Overflow

Problem in T-sql

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related