Simple pivot (Rows into Column)

Question

Hi,

I have a data like below

create table #table (_ID float, _Name varchar(20), _Code varchar(20));

insert into #table values (11111, 'ABC', 'I10')
insert into #table values (11111, 'XYZ', 'A78.5')
insert into #table values (11112, 'ABC', 'I11')
insert into #table values (11112, 'XYZ', 'B78.5')
insert into #table values (11113, 'ABC', 'I12')
insert into #table values (11113, 'XYZ', 'C78.5')

select * from #table;
drop table #table;

]2
I need to bring the row values into column (simple pivot)
i expecting the result like below

create table #Result (_ID float, ABC varchar(20), XYZ varchar(20));
insert into #Result values (11111, 'I10', 'A78.5')
insert into #Result values (11112, 'I11', 'B78.5')
insert into #Result values (11113, 'I12', 'C78.5')
select * from #Result;
drop table #Result;

Thanks

Answer 1

Try this:

SELECT ISNULL(abc._ID, xyz._ID) AS _ID,
       abc.ABC,
       xyz.XYZ
FROM (
    SELECT _ID, _Code AS ABC
    FROM #table
    WHERE _Name = 'ABC'
) AS abc
FULL JOIN (
    SELECT _ID, _Code AS XYZ
    FROM #table
    WHERE _Name = 'XYZ'
) AS xyz

Answer 2

This seems to work too:

select p.* from #table
pivot ( max(_Code) for _Name in ([ABC], [XYZ]) ) p

Answer 3

Here is a solution that users standard SQL and avoids Microsoft proprietary PIVOT operator, which I don't find very useful. The pattern below is a little more verbose, but it is more straightforward and easier to extend.

SELECT _ID, MIN(CASE _Name WHEN 'ABC' THEN _Code END) AS ABC,
            MIN(CASE _Name WHEN 'XYZ' THEN _Code END) AS XYZ
FROM   #table 
GROUP  BY _ID

By the way, float is an odd choice for an ID column...

Answer 4

Hi @kasim mohamed ,

Thank you so much for posting here in Microsoft Q&A.

You could refer other experts' answers.

In case you have many columns in your table to pivot, you could refer below dynamic way:

 declare @sql nvarchar(max)  
      
 select @sql=STUFF(( SELECT distinct  ',['+_Name+']'  FROM #table FOR XML PATH('') ), 1, 1, '')  
  
 set @sql=N' select p.* from #table  
 pivot ( max(_Code) for _Name in ('+@sql+')) p'  
      
 EXECUTE sp_executesql  @sql  

Output:

_ID	ABC	XYZ  
11111	I10	A78.5  
11112	I11	B78.5  
11113	I12	C78.5  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.