Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

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

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

Posting Yak Master

129 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 ?


In (Som, Ni, Yak)

17689 Posts

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

Time is always against us

Go to Top of Page

Posting Yak Master

129 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

Flowing Fount of Yak Knowledge

2242 Posts

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

    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


Edited by - bandi on 11/12/2012 00:43:11
Go to Top of Page
  Previous Topic Topic Next 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.08 seconds. Powered By: Snitz Forums 2000