January 27, 2010 at 2:48 pm
I have a one to many table and am seeking solution to get desired output (report).
Comment: I eventually want to get this report into Crystal reports. I am thinking I can create a view in SQL then link to the view Via Crystal Reports.Note the (2) dates for UserID = 16 within the same year. I may only require the most current date. If possible a solution to retrieve both of these dates and or a solution to just retrieve the most current or max date would be appreciated. I have entered the word blank just as a place holder to help separate the data in each column as it appears all close together in preview window.
Sample table:
RowID UserID Decision Decision_Date
1 21 8 5/10/2009
2 21 12 6/14/2009
3 43 16 6/21/2009
4 43 16 9/12/2009
Desired Report Result:
UserID Decision8 Decision12 Decision16
21 5/10/2009 6/14/2009 Blank
43 Blank Blank 6/21/2009
43 Blank Blank 9/12/2009
Thanks for having a look at my issue.
January 27, 2010 at 2:54 pm
It's more an issue of CrossTabs or even DynamicCrossTabs.
I gues it can be done in CrystalReports, but I'm not sure (never worked with it...).
You might want to have a look at the two related links in my signature regarding detailed information about CrossTabs.
January 27, 2010 at 3:22 pm
you do not have to do it in a view or specific sql statement if you are using crystal, you can base the report on a stored procedure.
However, If you want the max date then you can use pivot. See below
create table #test(
RowID int,
UserID int,
Decision int,
Decision_Date datetime)
insert into #test
(rowid,userid,decision,decision_date)
values(1, 21, 8, '5/10/2009')
insert into #test
(rowid,userid,decision,decision_date)
values(2, 21, 12, '6/14/2009')
insert into #test
(rowid,userid,decision,decision_date)
values(3, 43, 16, '6/21/2009')
insert into #test
(rowid,userid,decision,decision_date)
values(4 ,43, 16, '9/12/2009')
select
*
from
(
select
userid,decision,decision_date
from #test
) DataTable
PIVOT
(
max(decision_date)
FOR decision
IN (
[8],[12],[16] )
) PivotTable
January 27, 2010 at 3:31 pm
Thanks for response - However, I am looking for a dynamic solution. Your suggestion creates a new table based only the data that I provided as sample data. I have over 700 rows of data that I need to be able to sort as metioned in my original post.
Regards
January 27, 2010 at 4:55 pm
ColdPolarBear (1/27/2010)
Thanks for response - However, I am looking for a dynamic solution. Your suggestion creates a new table based only the data that I provided as sample data. I have over 700 rows of data that I need to be able to sort as metioned in my original post.Regards
That's why I referred to the two links in my signature.
Did you have a look at it?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply