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.
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 #TempWhen I run the above query, I get the result set in the following way:Company_Code Employee_Code Indi_Code100 210 310100 211 311But 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 311Can 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_CodeFROM cte c1 JOIN cte1 c2 ON c1.Company_Code = c2.Company_Code AND rn = 1[/code]--Chandu |
|
|
|
|
|
|
|