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, Jul 07 '11 at 17:43
@JNK I did refer http://stackoverflow.com/questions/6614048/error-in-case-statement But the query I wrote is not working — , Jul 07 '11 at 17:46

t-clausen.dk · Accepted Answer · 2011-07-07T21:00:48.953

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

@ t-clause It works fine for the above sample data but it fails if one reponse_ID has different order values — , Jul 07 '11 at 18:32
I tried to adjust my script to work for different orders sets — t-clausen.dk, Jul 07 '11 at 18:48

score 1 · Answer 2 · answered Jul 07 '11 at 17:50

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 07 '11 at 17:50

Pittsburgh DBA

6,672
2
39
68

score 0 · Answer 3 · edited May 23 '17 at 11:55

0

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?

edited May 23 '17 at 11:55

Community

1
1

answered Jul 07 '11 at 17:44

Dercsár

1,636
2
14
26

1

Although I did it in my script – t-clausen.dk Jul 07 '11 at 18:12

score -1 · Answer 4 · edited Aug 29 '13 at 17:17

-1

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/

edited Aug 29 '13 at 17:17

Martin Smith

438,706
87
741
845

answered Jul 07 '11 at 17:56

dave

1,520
11
8

Problem in T-sql

4 Answers4