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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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