July 1, 2010 at 3:27 am
I need to find the 4 last reports from a company and ad them to 4 different colums of my "denormalized" table.
The scenario is like this:
Source: "ControlReport"
CompanyID ReportID ReportDate
10 123 2007-09-19
10 234 2005-07-28
10 345 2004-10-08
10 456 2003-11-05
10 567 2002-03-04
11 111 2007-08-20
11 222 2005-06-11
11 333 2004-09-03
11 444 2003-12-01
11 555 2002-05-14
Destination "Denormalized"
CompanyID Report1 Report2 Report3 Report4
10 123 234 345 456
11 111 222 333 444
In my current solution I am using 2 Cursors "ReportCursor" and "CompanyCursor". This solution is not performing very well (the "ControlReport" table is huge with 40.000+ Companies and 10+ reports per Company).
Any input on a solution?
Thanks,
Jesper W
July 1, 2010 at 4:01 am
Hi there, try this:
declare @datatable table
(
CompanyID int,
ReportID int,
ReportDate smalldatetime
)
insert into @datatable (CompanyID ,ReportID, ReportDate)
select 10 ,123 ,'2007-09-19'
union all select 10 ,234 ,'2005-07-28'
union all select 10 ,345 ,'2004-10-08'
union all select 10 ,456 ,'2003-11-05'
union all select 10 ,567 ,'2002-03-04'
union all select 11 ,111 ,'2007-08-20'
union all select 11 ,222 ,'2005-06-11'
union all select 11 ,333 ,'2004-09-03'
union all select 11 ,444 ,'2003-12-01'
union all select 11 ,555 ,'2002-05-14'
;with cte as
(
select CompanyID ,ReportID,
row_number() over (partition by CompanyID order by ReportDate desc) RN
from @datatable
)
select * from
( select * from cte where RN <= 4 ) pivot_handle
pivot
(MAX(ReportID) for RN in ([1],[2],[3],[4])) pivot_table
July 1, 2010 at 6:03 am
Thank you very much.... Quick and effective ansver!!
While implementing the solution i wonder: Is it possible to show the dates in the table as well?
Like:
CompanyID ReportDate1 Report1 ReportDat2 Report2 ReportDate3 Report3 ReportDate4 Report4
10 2007-09-19 123
10 2005-07-28 234
10 2004-10-08 345
10 2003-11-05456
112007-08-20111
11 2005-06-11 222
11 2004-09-03 333
11 2003-12-01444
Thanks,
Jesper W
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply