November 9, 2006 at 4:45 am
Hi I am looking for a bit of guidance with an sql query I need to build a query to format my data in the following way
http://www.geocities.com/liverpool_anfieldreds/Images/target_results.JPG
but the data is in the following format using this sql
http://www.geocities.com/liverpool_anfieldreds/Images/current_results.JPG
Select IsNull(p.firstName+ ' ','') + IsNull(p.MiddleName+ ' ','') + IsNull(p.LastName,'') as 'Player',
rzd.Target,
rzd.Total,
DATENAME(WEEKDAY, rzd.Date) as 'Day'
From RedZoneData rzd
Join Person p on p.ID = rzd.PlayerID
Where rzd.PlayerID in (3 ,2)
And rzd.Date Between (Select StartDate From RedZoneWeeklyMapping
Where ID = 15)
And (Select EndDate From RedZoneWeeklyMapping
Where ID = 15)
Order By p.ID, rzd.Date
and im not really sure if its possible to change it around so the days are at the top and totals in their cols etc any help or advice would be appreciated thanks in advance
Tim
November 9, 2006 at 9:17 am
Use the Case function
Something like?
Select IsNull(p.firstName+ ' ','') + IsNull(p.MiddleName+ ' ','') + IsNull(p.LastName,'') as 'Player',
min(case when DATENAME(WEEKDAY, rzd.Date) = 'Monday' then rzd.Total end) as 'Monday',
min(case when DATENAME(WEEKDAY, rzd.Date) = 'Monday' then rzd.Target end) as 'MondayTarget',
min(case when DATENAME(WEEKDAY, rzd.Date) = 'Tuesday' then rzd.Total end) as 'Tuesday',
min(case when DATENAME(WEEKDAY, rzd.Date) = 'Tuesday' then rzd.Target end) as 'TuesdayTarget',
...
From RedZoneData rzd
Join Person p on p.ID = rzd.PlayerID
Where rzd.PlayerID in (3 ,2)
And rzd.Date Between (Select StartDate From RedZoneWeeklyMapping
Where ID = 15)
And (Select EndDate From RedZoneWeeklyMapping
Where ID = 15)
Group by IsNull(p.firstName+ ' ','') + IsNull(p.MiddleName+ ' ','') + IsNull(p.LastName,'')
Order By p.ID, rzd.Date
November 9, 2006 at 9:32 am
thanks alot man thats perfect very much appreciated
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
