How to Pivot SQL Server Table with multiple column values?

Question

How do I get the expected result using the SQL PIVOT function. I tried the below query to do this but I am getting only one record in the NAME column.

Sample Data:

    SNO       TYPE     ENTRY            
    1         Name     Andy         
    1         Name     Kevin            
    1         Active   Yes                   
    1         Value    50           
    2         Name     Andy         
    2         Name     Julia            
    2         Active   No           
    2         Value    45

Expected Result:

   SNO    NAME        ACTIVE    VALUE       
   1    Andy, Kevin    Yes       50          
   2    Andy, Julia    No        45

Query I tried:

            select SNO,Name,Active,Value 
            from 
                (
                select * from tbl1
                ) as PivotData

                Pivot 
                (
                max(ENTRY) for TYPE in([Name],[Active],[Value])
                ) as Pivoting

John Cappelletti · Accepted Answer · 2018-10-10 19:02:13Z

1

Assuming ACTIVE and VALUE do not have multiple values. We just exclude NAME from the PIVOT and resolve it via the standard XML STUFF

Example

Select SNO
      ,Name  = Stuff((Select Distinct ',' +Entry From @YourTable Where SNO=Pvt.SNO and [TYPE]='Name' For XML Path ('')),1,1,'')
      ,Active
      ,Value 
 From   YourTable A
 Pivot ( max(ENTRY) for TYPE in (Active,Value) ) Pvt

Returns

SNO Name        Active  Value
1   Andy,Kevin  Yes     50
2   Andy,Julia  No      45

answered Oct 10, 2018 at 19:02

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

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

2 Comments

Andy Over a year ago

That's Awesome John! Although my original query was too complicated and it had 15 columns with multiple values like this one and the solution worked for me perfectly. I Saved a lot of time. Tons of thanks for this.

John Cappelletti Over a year ago

@Andy Always happy to help :)

Collectives™ on Stack Overflow

How to Pivot SQL Server Table with multiple column values?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related