SQL Server pivot with string concatenation

Question

I have data like below

| Name      | Subject   | Answer                |
|:----------|:----------|:---------------------:|
|Pranesh    |Physics    |Numerical Problems     |
|Pranesh    |Physics    |Other                  |
|Pranesh    |Chemistry  |Understanding Concepts |
|Pranesh    |Chemistry  |Organic chemistry reactions
|Pranesh    |Maths      |Lack of understanding  |
|Pranesh    |Maths      |Insufficient practice  |
|Pranesh    |Maths      |Other                  |

Using this SQL query:

select * 
from 
    (select  
         l.FullName Name, sq.Title Subject, cAns.Name Answer 
     from 
         Answer as sa
     left join
         Question AS sq on sq.ID = sa.QuestionID
     left join 
         Master as cAns on cAns.ID = sa.AnswerID
     left join 
         Profile as l on l.ID = sa.ProfileID) src
pivot
    (max(Answer)
        for Subject in ([Physics], [Chemistry], [Maths], [Biology],[ComputerScience], [CommonUnderstanding])) piv;

I am able to get the data as follows

How to concatenate answer column of same subject and display the same like in above screen shot ?

expected output should be

Chanukya
– Chanukya

2016-12-02 12:05:52 +00:00
Commented Dec 2, 2016 at 12:05 — Chanukya
– Chanukya, Commented Dec 2, 2016 at 12:05

DatabaseCoder · Accepted Answer · 2016-12-02 12:24:55Z

2

I have concatenated the answer list first per name and subject, then applied pivoting -

declare @temp table (name varchar(100), subject varchar(100), answer varchar(100))

insert into @temp
            select 'Pranesh','Physics'  ,'Numerical Problems'
union all   select 'Pranesh','Physics'  ,'Other'
union all   select 'Pranesh','Chemistry','Understanding Concepts'
union all   select 'Pranesh','Chemistry','Organic chemistry reactions'
union all   select 'Pranesh','Maths'    ,'Lack of understanding'
union all   select 'Pranesh','Maths'    ,'Insufficient practice'
union all   select 'Pranesh','Maths'    ,'Other'
union all   select 'Ramesh','Biology'   ,   'Other'
union all   select 'Ramesh','Biology'   ,   'Science'

;with cte as (select distinct name, subject from @temp)
select * from
(
    select
        c.name,
        c.subject,
        answer = stuff((select ',' + answer from @temp t where t.name=c.name and t.subject=c.subject for xml path('')), 1, 1, '')
    from cte c
) src
pivot
(
    max(answer) for subject in ([Physics], [Chemistry], [Maths], [Biology],[ComputerScience],[CommonUnderstanding])
) piv

answered Dec 2, 2016 at 12:24

DatabaseCoder

2,0522 gold badges15 silver badges22 bronze badges

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

6 Comments

Pranesh Janarthanan Over a year ago

I need to study your query and try to get the result from my code. give me some time

Pranesh Janarthanan Over a year ago

I Accept your answer +1, can you brief me the usage of @temp table here, what happens to the memory after the execution etc..? performance wise how this query is going to function?

DatabaseCoder Over a year ago

@temp table is used to store data same as you have. Please ignore temp table and use your permanent table name in place of temp table. For this answer, i just want string to be concatenated before pivoting.

DatabaseCoder Over a year ago

And to concatenate the string , i need distinct set of name and subject, so i use CTE for that.

Pranesh Janarthanan Over a year ago

my actual code is a stored procedure, which includes 10 left join with 15 columns as output. So i used your code as it is for all those select columns. i dont know how to implement without using temp table. can you help me?

|

Krish · Accepted Answer · 2016-12-02 12:13:12Z

0

Try this:-

SELECT ANS, PHYSICS,CHEMISTRY,MATHS
FROM
(
SELECT  L.FULLNAME NAME, SQ.TITLE SUBJECT, CANS.NAME ANSWER FROM ANSWER AS SA
LEFT JOIN QUESTION AS SQ ON SQ.ID = SA.QUESTIONID
LEFT JOIN MASTER AS CANS ON CANS.ID = SA.ANSWERID
LEFT JOIN PROFILE AS L ON L.ID = SA.PROFILEID
) AS T
UNPIVOT
(
  COMBINE_COLUMN_VALUES FOR ANS IN (ANSWER)
) AS U
PIVOT
(
  MAX(COMBINE_COLUMN_VALUES) FOR [SUBJECT] IN (PHYSICS,CHEMISTRY,MATHS)
) AS P

edited Dec 2, 2016 at 12:13

answered Dec 2, 2016 at 11:58

Krish

398 bronze badges

2 Comments

Pranesh Janarthanan Over a year ago

Pranesh Janarthanan Over a year ago

can you explain COMBINE_COLUMN_VALUES ..?

Collectives™ on Stack Overflow

SQL Server pivot with string concatenation

2 Answers 2

6 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related