swap few rows in sql server

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

, +

I have question about SQL Server

Source: emp

id      | name  | check |deptname 
100     |  a    |  1    |ceo   
100     |  b    |  2    |hr
100     |  c    |  3    |po
100     |  d    |  5    |no
101     |  a    |  1    |pm
101     |  b    |  5    |ceo
102     |  a    |  1    |rn
102     |  b    |  2    |han

Here same id have check 2 and 5 values then we need to replace check values to 2 check values for that id.

Based on above table I want load/output data into target table like below

Target : emp1

id      | name  | check |deptname
100     |  a    |  1    |ceo
100     |  d    |  2    |hr
100     |  c    |  3    |po
101     |  a    |  1    |pm
101     |  b    |  5    |ceo
102     |  a    |  1    |rn
102     |  b    |  2    |han

and I tried like below

SQL

select 
    a1.id,
    a1.name,
    isnull(a2.[check],a1.[check]) as [check]
from 
    emp as a1
left outer join 
    emp as a2 on a2.id = a1.id
              and a1.[check] in (2,5)
              and a2.[check] in (2,5)
              and a2.[check] <> a1.[check]
where 
    a2.id is null
    or (a1.[check] = 5
        and a2.[check] = 2)

That query does not return the right result.

Please tell me how to write query to get the expected output in SQL Server

Posted 26-Sep-15 8:50am

vasu12

Updated 26-Sep-15 9:32am

Patrice T

v2

Add a Solution

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2015-09-26T11:26:00

Check this:

SQL

DECLARE @emp TABLE(id INT, [name] VARCHAR(30), [check] INT, deptname VARCHAR(30))
INSERT INTO @emp (id, [name], [check], deptname)
VALUES(100, 'a', 1, ' ceo'),
(100, 'b', 2, ' hr'),
(100, 'c', 3, ' po'),
(100, 'd', 5, ' no'),
(101, 'a', 1, ' pm'),
(101, 'b', 5, ' ceo'),
(102, 'a', 1, ' rn'),
(102, 'b', 2, ' han')

UPDATE t1 SET [name] = t2.[name]
--SELECT t1.id, t2.[name], t1.[check], t1.deptname
FROM (
	SELECT id, [name], [check], deptname
	FROM @emp
	WHERE id = 100 AND [check]=2
	) AS t1 INNER JOIN  (
		SELECT id, [name], [check], deptname
		FROM @emp
		WHERE id = 100 AND [check]=5
	) AS t2 ON t1.id = t2.id 

DELETE 
FROM @emp
WHERE id = 100 AND [check]=5

SELECT *
FROM @emp

Wendelius · Answer 2 · 2015-09-26T11:37:00

Not sure if I understood the requirements correctly, but have a try with something like this

SQL

select a.id,
       a.name,
       a.[check],
       a.deptname
from emp a
where not exists (select 1
                  from emp b
                  where b.id = a.id
                  and   b.[check] = 2)
or   not exists (select 1
                  from emp b
                  where b.id = a.id
                  and   b.[check] = 5)
union all
select a.id,
       a.name,
       case a.[check]
          when 5 then 2
          else a.[check]
       end,
       a.deptname
from emp a
where exists (select 1
                  from emp b
                  where b.id = a.id
                  and   b.[check] = 2)
and  exists (select 1
                  from emp b
                  where b.id = a.id
                  and   b.[check] = 5)
and a.[check] <> 2
order by 1,3

I'm wondering about the deptname. In your example data all other values were from the row having originally value 5 but the deptname was from the row having check value 2. Not sure if this was intentional but if it was then that value should be fetched separately.

swap few rows in sql server

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

swap few rows in sql server

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us