0

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,

Improve this question
1
  • If you want an update, you should ask another question. Commented May 19, 2016 at 2:37

2 Answers 2

Reset to default
3

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);
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks, Gordon. I used the second solution earlier but I got stuck at this point, Can you check my updated question. Thanks
1

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.

Improve this answer

2 Comments

Hi Taz, Can you please check the code that I added just now.
Hi Praveen, I am still getting the same results. a | xx | mango### b | orange| xx### c | xx | apple### d | xx | banana### a | xx | mango

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.