Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
weepingboil
Starting Member
5 Posts |
 Posted - 2008-11-24 : 14:37:31
|
| Hi - hoping someone can help!I have a simple table that looks a bit like this...RowID | ColumnID | Value-------+------------+----------1 | 1 | 01/01/081 | 2 | Green1 | 3 | Heavy2 | 1 | 01/02/082 | 2 | Blue2 | 3 | HeavyI need a query that returns...Date | Colour | Weight----------+-----------+-----------01/01/08 | Green | Heavy01/02/08 | Blue | Heavy(I know it's a weird table - it's the UserDefinedTable in DotNetNuke if you know it)I can get it to work in Access - but only by using TRANSFORM and PIVOT which I understand I can't use in SQL2000Oh - and to add a futher challenge I only want the top 5 sorted by the date.This is way beyond me - any help would be gratefully appreciated.(I can get close by using CASE and GROUP BY - but that needs an aggregate function... and then I get stuck!) |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-24 : 14:59:10
|
| Declare @t table(RowID int, ColumnID int, [Value] char(12))insert @t select 1 , 1 , '01/01/08' union allselect 1 , 2 , 'Green' union allselect 1 , 3 , 'Heavy' union allselect 2 , 1 , '01/02/08' union allselect 2 , 2 , 'Blue' union allselect 2 , 3 , 'Heavy'Select max(case when columnID = 1 then value end) as Date,max(Case when columnID = 2 then value end) as Colour,max(Case when columnID =3 then value end) as Weightfrom @tGroup by RowID |
 |
|
|
weepingboil
Starting Member
5 Posts |
Posted - 2008-11-24 : 16:15:45
|
| Brilliant,But how can I order the list by date (especially as it's stored as a text)? |
|
|
|
weepingboil
Starting Member
5 Posts |
Posted - 2008-11-24 : 16:21:50
|
| ...especially as the dates in the table have been entered in UK format:dd/mm/yy!!! |
|
|
|
weepingboil
Starting Member
5 Posts |
Posted - 2008-11-24 : 16:45:11
|
| Woohoo - I fixed the date bit.I now haveSET DATEFORMAT dmySELECTMAX(CASE WHEN UserDefinedFieldId = 8 THEN cast(FieldValue as datetime) END) as Date,MAX(CASE WHEN UserDefinedFieldId = 9 THEN FieldValue END) as Event,MAX(CASE WHEN UserDefinedFieldId = 10 THEN FieldValue END) as YearGroup,MAX(CASE WHEN UserDefinedFieldId = 11 THEN FieldValue END) as StaffFROM UserDefinedDataGROUP BY UserDefinedRowIdORDER BY DateHow can I select just the records from today's date?(I get errors if I use a WHERE on the 'Date' column...) |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-24 : 16:58:54
|
quote:
Originally posted by weepingboil
Woohoo - I fixed the date bit.I now haveSET DATEFORMAT dmySELECT * from(SELECTMAX(CASE WHEN UserDefinedFieldId = 8 THEN cast(FieldValue as datetime) END) as Date,MAX(CASE WHEN UserDefinedFieldId = 9 THEN FieldValue END) as Event,MAX(CASE WHEN UserDefinedFieldId = 10 THEN FieldValue END) as YearGroup,MAX(CASE WHEN UserDefinedFieldId = 11 THEN FieldValue END) as StaffFROM UserDefinedDataGROUP BY UserDefinedRowId)twhere t.Date >= 'Yourdate'order by DateHow can I select just the records from today's date?(I get errors if I use a WHERE on the 'Date' column...)
|
|
|
|
weepingboil
Starting Member
5 Posts |
Posted - 2008-11-24 : 17:02:55
|
| Done! Many thanks for your help!(Recorded here for posterity...)SET DATEFORMAT dmyDECLARE @poopy TABLE (Date DateTime, Event char(100), YearGroup char(100) ,Staff char (100))INSERT INTO @poopy (Date, Event, YearGroup, Staff)SELECTMAX(CASE WHEN UserDefinedFieldId = 8 THEN cast(FieldValue as datetime) ELSE '01/01/08' END) as Date,MAX(CASE WHEN UserDefinedFieldId = 9 THEN FieldValue ELSE '0' END) as Event,MAX(CASE WHEN UserDefinedFieldId = 10 THEN FieldValue ELSE '10' END) as YearGroup,MAX(CASE WHEN UserDefinedFieldId = 11 THEN FieldValue ELSE '0' END) as StaffFROM UserDefinedDataGROUP BY UserDefinedRowIdSELECT TOP 10 CONVERT(varchar,Date,103),Event,YearGroup,Staff FROM @poopyWHERE Date >= GetDate()ORDER BY Date |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-24 : 17:05:01
|
Welcome!! |
|
|
|
|
|
|
|
|
out of my depth