Pulling multiple rows of a table into one row

[IronIke]

Hi,

I am having trouble figuring out how to retrieve the data I need from a SQL Server 2000 database.

My table looks like the following:

RecId Int,
EmpID Int,
StartDate datetime,
Description varchar(30)

There may be multiple records with the same EmpId. What I want to do is retrieve the top three records per empid, ordered by Startdate, in a single row. For example, if my table contains the following:


100, 1001, '2001-01-01', 'Description 1a'
101, 1001, '2001-05-01', 'Description 1b'
102, 1001, '2001-07-01', 'Description 1c'
103, 1001, '2000-06-01', 'Description 1d'
104, 1002, '2001-09-01', 'Description 2a'
105, 1002, '2001-03-01', 'Description 2b'
106, 1002, '2001-04-01', 'Description 2c'

How can I get the following as a result of my query:

1001, '2001-07-01', 'Description 1c', '2001-05-01', 'Description 1b', '2001-01-01', 'Description 1a'

1002, '2001-09-01', 'Description 2a', '2001-04-01', 'Description 2c', '2001-03-01', 'Description 2b'

Now I have tried the following, which works but takes far too much time:

Select EmpId,

(Select Top 1 StartDate From myTable Where EmpID = Main.EmpId Order by StartDate Desc) As Date1,
(Select Top 1 Description From myTable Where EmpID = Main.EmpId Order by StartDate Desc) As Desc1,

(Select Top 1 StartDate From myTable TB1 where TB1.EmpID = Main.EmpId
And TB1.RecId Not In (Select Top 1 RecId From myTable TB2 Where TB2.EmpID = Main.EmpId Order by TB2.StartDate Desc)
Order by StartDate Desc) As Date2,

(Select Top 1 Description From myTable TB3 Where TB3.EmpId = Main.EmpID
And TB3.RecId Not In (select Top 1 RecId From myTable TB4 Where TB4.EmpId = Main.EmpID Order by TB4.StartDate Desc)
Order by StartDate Desc) as Desc2,

(Select Top 1 StartDate From myTable TB5 Where TB5.EmpId = Main.EmpID
And TB5.RecId Not In (Select Top 2 RecId From myTable TB1 where TB1.EmpID = Main.EmpId
Order by StartDate Desc)
Order by StartDate Desc) As Date3,

(Select Top 1 Description From MyTable TB7 Where TB7.EmpId = Main.EmpId
And TB7.RecId Not In (Select Top 2 RecId From myTable TB1 where TB1.EmpID = Main.EmpId
Order by StartDate Desc)
Order by StartDate Desc) As Desc3

From myTable Main
Group by EmpId

Is there another approach that would be more efficient?

 

[ClaireHCM]

So far I can solve the first requirment

select * from a where a.startdate in (select top 3 aa.startdate from a AA where aa.empid = a.empid ) order by empid,startdate desc

But for secoding one...still processing!

 

[bertrandkis]

Here is my 2 cents about your problem:

-- first get all distinct employerid
declare @pktable table(pk int,ids int identity)
declare @ctr int,@maxids int
--create a temp table to hold your concatenated rows
Create Table #datatable (pk int identity,col1 varchar(30),col2 varchar(30),col3 varchar(30),col4 varchar(30),
col5 varchar(30),col6 varchar(30),col7 varchar(30))
declare @util varchar(20) --variable to use when needed
--Create cursor of distinct Empid
declare foocursor cursor for select distinct EmpId from MyTable

open foocursor
fetch next from foocursor into @util
While @@fetch_status<>-1
Begin
set @ctr=1
While @ctr<4
Begin
Insert into @pktable (pk) select top 1 recid from MyTable where EmpId=@util and recid not in(select pk from @pktable)Order by Startdate

set @maxids=(select max(ids) from @pktable)

if @ctr=1
Insert into #datatable (col1,col2,col3) select top 1 Empid,StartDate,[Description] from MYTable where Empid=@util and recid =(select pk from @pktable where ids=@maxids)
if @ctr=2
Update #datatable set col4=(select top 1 Startdate from MYtable where recid =(select pk from @pktable where ids=@maxids) ),col5=(select top 1 [Description] from MYTable where recid =(select pk from @pktable where ids=@maxids))where pk=(select max(pk) from #datatable)
if @ctr=3 --Stop counter at 3 because you want top 3 records
Update #datatable set col6=(select top 1 StartDate from MyTable where recid = (select pk from @pktable where ids=@maxids)),col7=(select top 1 [Description]from MYTable where recid = (select pk from @pktable where ids=@maxids))
where pk=(select max(pk) from #datatable)
set @ctr=@ctr+1
end
fetch next from foocursor into @util
end
select * from #datatable
close foocursor
deallocate foocursor
drop table #datatable

This script was tested with the data that you supplied above and it works fine.
Just copy and paste the script in query analyser,run it and observe the output; then come back here and mark this tip as an Expert tip. will you?

 

[Rhys666]

why can't you simply left join the table to itself twice?

EmpID Int,
StartDate datetime,
Description varchar(30)

SELECT
tbl1.EmpId,
tbl1.StartDate,
tbl1.Description,
tbl2.StartDate,
tbl2.Description,
tbl3.StartDate,
tbl3.Description
FROM
TableName tbl1 LEFT OUTER JOIN TableName tbl2
ON tbl1.EmpId = tbl2.EmpId
LEFT OUTER JOIN TableName tbl3
ON tbl1.EmpId = tbl3.EmpId
ORDER BY
tbl1.StartDate,
tbl2.StartDate,
tbl3.StartDate

Use coalesce, to replace NULLS if required


Rhys

Be careful that the light at the end of the tunnel isn't a train coming the other way.

 

[bertrandkis]

Dear Rhys666, I tried your query and it does not produce the disired out put.One of us might have misunderstood what IronIke wants to achieve. The table to work from looks like this:
REcId EmpId StartDate Description
100 1001 2001-01-01 Description 1a
101 1001 2001-05-01 Description 1b
102 1001 2001-07-01 Description 1c
103 1001 2000-06-01 Description 1d
104 1002 2001-09-01 Description 2a
105 1002 2001-03-01 Description 2b
106 1002 2001-04-01 Description 2c

My Query Produces this output which consists of 2 records:
1 1001 2000-06-01 Description 1d 2001-01-01
Description 1a 2001-05-01 Description 1b
2 1002 2001-03-01 Description 2b 2001-04-01 Description 2c 2001-09-01 Description 2a
I ran your query on the same table and it produces 91 records; so I don't think that you got it right.
The issue here is to concatenate rows and not to duplicate columns and mix rows.
simply copy the table execute my query and your query against it,the results will speak for themselves.