Help with SQL Query - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Help with SQL Query

Author

Topic

Looper
Yak Posting Veteran

68 Posts

Posted - 2010-10-12 : 08:50:40

I have a table with the following fields

Id, EmployeeId, RegId

i have two rows in the database for an employee.

1, 1, 15
2, 1, 16

I want to write a bit of sql so that I can extract like follows:

EmployeeId, RegId1, RegId2
1, 15, 16

RegId1 will always be the earliest Id

Can anyone help

Sachin.Nand

2937 Posts

Posted - 2010-10-12 : 09:08:08

How many regid can a paticular employeeId have?

PBUH

Looper
Yak Posting Veteran

68 Posts

Posted - 2010-10-12 : 09:34:49

upto 2 RegIds per employeeId

Sachin.Nand

2937 Posts

Posted - 2010-10-12 : 12:06:37

[code]
declare @tbl table (Id int, EmployeeId int, RegId int)
insert @tbl
select 1, 1, 15 union
select 2, 1, 16 union
select 2, 2, 16

select * from @tbl

select
employeeid,
isnull(max(case when rid=1 then regid end),0)regid1,
isnull(max(case when rid=2 then regid end),0)regid2
from
(
select *,row_number()over(partition by employeeid order by id)rid from @tbl
)T
group by EmployeeId
[/code]

PBUH

Looper
Yak Posting Veteran

68 Posts

Posted - 2010-10-12 : 12:32:31

Thanks

This part where you have specified the data:
select 1, 1, 15 union
select 2, 1, 16 union
select 2, 2, 16
does not help if the table has over 1000 rows. Sorry I should have said that the two rows i provided was an example of how the table looks in general. But how can i extract all out of the table in the format I specified?

Sachin.Nand

2937 Posts

Posted - 2010-10-12 : 12:36:08

It's just some sample data.You need to run the query I posted against your table replacing it with your table name & column name in the query.

PBUH

Looper
Yak Posting Veteran

68 Posts

Posted - 2010-10-12 : 12:44:42

of course it is, silly me. it has been a long day

Thanks