SQL - Duplicate row values into column values 2

[sajisher]

Hi,

I have a problem with duplicate rows. There are two tables - Emp and Rep.

EMP table

EmpName Rep1 Rep2 Rep3
John 1 2 null
Jim 1 2 3

REP table

RepNo RepName
1 Aril
2 Ben
3 Cate

I want the result as

EmpName Rep1 Rep2 Rep3
John Aril Ben null
Jim Aril Ben Cate

When I use a normal join between Reps in Emp table and Repno in Rep table, the result is duplicate rows like below.

EmpName Rep1 Rep2 Rep3 repNo
John Aril null null 1
John null Ben null 2
Jim Aril null null 1
Jim null Ben null 2
Jim null null Cate 3

The SQL I am using is:

Select empName,
case rep1 when r.repNo then r.repName End as Rep1,
case rep2 when r.repNo then r.repName End as Rep2,
case rep3 wehn r.repNo then r.repName End as Rep3,
repNo FROM Emp
left join Rep r on emp.Rep1 = r.repNo OR
emp.Rep2 = r.repNo OR
emp.Rep3 = r.repNo

Will very much appreciate ideas on getting the result in 2 rows please.


thanks
saj

 

[bborissov]

NOT TESTED!!!
And after a lots of beer )))
So use it on your own risk

Select Emp.empName,
       MAX(CASE WHEN rep1 = r.repNo
                     THEN r.repName 
                END) as Rep1,
       MAX(CASE WHEN rep2 = r.repNo
                     THEN r.repName 
                END) as Rep2,
       MAX(CASE WHEN rep3 = r.repNo
                     THEN r.repName 
                END) as Rep2,
       Emp.repNo 
FROM Emp
INNER join Rep r 
      on emp.Rep1 = r.repNo OR
         emp.Rep2 = r.repNo OR
         emp.Rep3 = r.repNo
GROUP BY Emp.repNo, Emp.empName

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[sajisher]

Hi Borislav,

Thanks for the quick response on a weekend! I tried the query and it says 'Incorrect syntax near '='. (Line 2).

Looks when does not take in a condition?? I tried to tweak a little but of no use.

thanks saj

 

[markros]

Try

declare @Emp table (ID int identity(1,1), EmpName varchar(30), Rep1 int NUll, Rep2 int Null, Rep3 int Null)
insert into @Emp (EmpName, Rep1, Rep2, Rep3) values ('John',    1,     2,   null), ('Jim',    1,     2,   3)
declare @Rep table (RepNo int identity(1,1), RepName varchar(30))
insert into @Rep (RepName) values ('Aril'),(' Ben'),('Cate')

;with cte1 as (select E.*, R.Repname from @Emp E left join @Rep R on E.Rep1 = R.RepNo),
 cte2 as (select E.*, R.Repname from @Emp E left join @Rep R on E.Rep2 = R.RepNo),
 cte3 as (select E.*, R.Repname from @Emp E left join @Rep R on E.Rep3 = R.RepNo)
 
 select cte1.EmpName, cte1.RepName, cte2.RepName, cte3.RepName from cte1 inner join cte2 on cte1.ID = cte2.ID inner join cte3 on cte1.ID = cte3.ID

 

[sajisher]

Borislav, I tried your query without r.RepNo in Select clause and Group By clause and voila the expected result came out!.

Markros, thanks for the query but I did'nt try the query as the tables are long established with lots of data in them.

This forum is wonderful, Thank you all guys!

saj

 

[markros]

Sure, but you don't have to create the tables - it was just for my test. You need these 3 CTEs and a select as a I showed.