How to select two rows in one on T-SQL?

Vasily Zaytsev 241 Reputation points
2022-09-27T08:47:34.703+00:00

I have this table
declare @T table(id int,type int, hrs int);
insert into @T values (1,1,5),(1,7,4),(2,1,3),(3,7,4)
How can I get this output?
id,hrs of type 1, hrs of type 7
1,5,4
2,3,NULL
3,NULL,4

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
0 comments No comments
{count} votes

Accepted answer
  1. CosmogHong-MSFT 23,716 Reputation points Microsoft Vendor
    2022-09-27T08:55:02.147+00:00

    Hi @Vasily Zaytsev
    Check this:

    declare @T table(id int,type int, hrs int);  
    insert into @T values (1,1,5),(1,7,4),(2,1,3),(3,7,4)  
      
    select id,[1] as [hrs of type 1], [7] as [hrs of type 7]  
    from @T  
    pivot(max(hrs)for type in([1],[7]))p  
      
    select id  
          ,max(case when type=1 then hrs end) as [hrs of type 1]  
    	  ,max(case when type=7 then hrs end) as [hrs of type 7]  
    from @T  
    group by id  
    

    Best regards,
    LiHong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    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.


0 additional answers

Sort by: Most helpful