T-sql help needed for the below issue :

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

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

T-sql help needed for the below issue :

Author

Topic

raaj
Posting Yak Master

129 Posts

Posted - 2012-11-11 : 23:39:19

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)

17689 Posts

Posted - 2012-11-11 : 23:45:35

what if you have more than 2 employee ?

KH
[spoiler]Time is always against us[/spoiler]

raaj
Posting Yak Master

129 Posts

Posted - 2012-11-12 : 00:03:49

For each company_code, there will be only 2 employee and Indi codes.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-12 : 00:42:17

[code]
;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
[/code]

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources