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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy