get two rows from two different select statements

问题:

I have a table something like below: table_a

  k | id | results 
--------------------
  a | 1  | mango
  b | 1  | orange
  c | 2  | apple
  d | 2  | banana
  a | 2  | mango

I have two select statements ans there results as below:

first select

select k, id, results from 
table_a where id = 1

Results:

  k | id | results 
--------------------
  a | 1  | mango
  b | 1  | orange

second select

select k, id, results from 
table_a where id = 2

Results:

  k | id | results 
--------------------
  c | 2  | apple
  d | 2  | banana
  a | 2  | mango

how can I get results as below:

  k | id |  abc  | xyz
------------------------
  a | 1  | mango | mango
  b | 1  | orange| xx
  c | 2  | xx    | apple
  d | 2  | xx    | banana

Thanks Gorgon, Praveen. I get results in both ways.

I have another table as below:

table_b

  k | 1  | 2 
--------------------
  a |    | 
  b |    | 
  c |    | 
  d |    | 

when I try to update table_b with the below query:

update table_b set 
abc = x.abc, xyz = x.xyz from (
select k, id, result as abc, 'xx' as xyz 
from table_a
where id = 1
union all
select k, id, 'xx' as abc, result as xyz
from table_a
where id = 2 ) x
where table_b.k = x.k

I am getting results as below:

table_b

  k |  1    | 2 
--------------------
  a | xx    | mango
  b | orange| xx
  c | xx    | apple
  d | xx    | banana

how can I get the below results table_b

  k |  1    | 2 
--------------------
  a | mango | mango
  b | orange| xx
  c | xx    | apple
  d | xx    | banana

Thanks,

回答1:

One method uses union all:

select k, id, results as abc, 'xx' as xyz 
from table_a
where id = 1
union all
select k, id, 'xx' as abc, results as xyz
from table_a
where id = 2;

Another just uses case:

select i, id,
       (case when id = 1 then results else 'xx' end) as abc,
       (case when id = 2 then results else 'xx' end) as xyz
from table_a
where id in (1, 2);

回答2:

Please check this method which is using CASE.

DECLARE @table TABLE
    (k CHAR (2),id INT,results VARCHAR(10))
INSERT INTO @table
    (k,id,results)
VALUES
    ('a',1,'mango'),('b',1,'orange'),('c',2,'apple'),('d',2,'banana'),('a',2,'mango')

SELECT 
    k, id,
   (CASE WHEN id = 1 THEN results ELSE 'xx' END) AS abc,
   (CASE WHEN id = 2 THEN results ELSE 'xx' END) AS xyz
FROM
    @table 

Can You please try the below code

 DECLARE @table_b TABLE
    (k CHAR (2),[1] VARCHAR(10),[2] VARCHAR(10))

 INSERT INTO @table_b
    (k)
 VALUES
    ('a'),('b'),('c'),('d'),('a')

 DECLARE @table TABLE
    (k CHAR (2),id INT,results VARCHAR(10))
 INSERT INTO @table
    (k,id,results)
 VALUES
    ('a',1,'mango'),('b',1,'orange'),('c',2,'apple'),('d',2,'banana'),('a',2,'mango')

 SELECT 
    k, id,
   (CASE WHEN id = 1 THEN results ELSE 'xx' END) AS abc,
   (CASE WHEN id = 2 THEN results ELSE 'xx' END) AS xyz
 FROM
    @table 


 UPDATE
   @table_b 
 set 
  [1] = x.abc, [2] = x.xyz from 
   (
     SELECT
       k, id, results as abc, 'xx' as xyz 
     from 
       @table
     where id = 1
     union all
     select 
       k, id, 'xx' as abc, results as xyz
     from 
       @table
     where id = 2 ) x

I believe this is the expected output you are looking for.