question

kasimmohamed-6993 avatar image
0 Votes"
kasimmohamed-6993 asked MelissaMa-msft commented

Simple pivot (Rows into Column)

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;

![97598-image.png][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;

97605-image.png
Thanks


[2]: /answers/storage/attachments/97599-image.png

sql-server-generalsql-server-transact-sql
image.png (6.5 KiB)
image.png (4.2 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @kasimmohamed-6993,

Could you please validate all the answers so far and provide any update?

If all are not working, please provide more sample data and expected output.

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered

This seems to work too:

 select p.* from #table
 pivot ( max(_Code) for _Name in ([ABC], [XYZ]) ) p
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @kasimmohamed-6993,

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.