SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 T-sql help needed for the below issue :
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

raaj
Posting Yak Master

128 Posts

Posted - 11/11/2012 :  23:39:19  Show Profile  Reply with Quote
Hi Guys,

I am facing the following issue in my SQL :
Here is the sample coding :

Create table #Temp
(Company_Code INT,
Employee_Code INT,
Indi_Code INT)

INSERT into #Temp Values (100,210,310)
INSERT into #Temp Values (100,211,311)

Select * from #Temp
When I run the above query, I get the result set in the following way:
Company_Code Employee_Code Indi_Code
100 210 310
100 211 311

But I want my result set to be in One row i.e in this way :
Company_Code Employee_Code Indi_Code New_Employee_Code New_Indi_Code
100 210 310 211 311

Can anyone give any suggestions to how to solve the above issue ?

Thanks,
Raaj

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 11/11/2012 :  23:45:35  Show Profile  Reply with Quote
what if you have more than 2 employee ?


KH
Time is always against us

Go to Top of Page

raaj
Posting Yak Master

128 Posts

Posted - 11/12/2012 :  00:03:49  Show Profile  Reply with Quote
For each company_code, there will be only 2 employee and Indi codes.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 11/12/2012 :  00:42:17  Show Profile  Reply with Quote

;WITH 
    cte AS (Select *, ROW_NUMBER() over(partition by company_code order by employee_code) rn from #Temp),
    CTE1 AS (select Company_code, Employee_Code, Indi_Code from cte where rn = 2)
SELECT c1.Company_Code, c1.Employee_Code, c1.Indi_Code, c2.Employee_Code AS New_Employee_Code, c2.Indi_Code AS New_Indi_Code
FROM cte c1 
JOIN cte1 c2 ON c1.Company_Code = c2.Company_Code AND rn = 1


--
Chandu

Edited by - bandi on 11/12/2012 00:43:11
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000