P
poalman
Hi,
I'm trying to make a query that groups a table on 2 columns, then
takes all the numbers from 1 of the ungrouped columns and puts them
into rows, with a flexible number of rows.
I haven't explained this especially well, I'll try posting a the start
and end points that I'm look for and hope someone can make some sense
of this because it's really stretching my SQL knowledge
Current Table:
Date Project SubProject Check
01/08/2007 J I 72%
01/08/2007 L None 75%
01/08/2007 Sa E 51%
01/11/2007 F None 42%
01/11/2007 J I 66%
01/01/2008 Sk None 46%
01/03/2008 C None 70%
01/03/2008 F None 100%
01/03/2008 J I 83%
01/03/2008 L None 85%
01/03/2008 M 2 93%
01/03/2008 M 3 85%
01/03/2008 R M 68%
01/03/2008 Sa E1 56%
01/06/2008 Sa E2 80%
Project SubProject Check1 Check2 Check3
J I 72% 66% 83%
L None 75% 85% 0%
Sa E1 51% 56% 0%
F None 42% 100% 0%
Sk None 46% 0% 0%
C None 70% 0% 0%
M 2 93% 0% 0%
M 3 85% 0% 0%
R M 68% 0% 0%
Sa E2 80% 0% 0%
So what has happened is its grouped the project and subproject (GROUP
BY Project, SubProject)
and the lines that were lost by the grouping, it has added the score
into a new column (check2, check 3, ect.) if there was another J & I
Check then the new table would need an additional column (Check4).
So basically I need to add as many columns as there are Projects with
the same SubProject.
Also the checks need to appear in order of the dates, so check 1 is
the earliest.
Any help would be HUGELY appreciated as I just can't crack this one!
Many Thanks!!
I'm trying to make a query that groups a table on 2 columns, then
takes all the numbers from 1 of the ungrouped columns and puts them
into rows, with a flexible number of rows.
I haven't explained this especially well, I'll try posting a the start
and end points that I'm look for and hope someone can make some sense
of this because it's really stretching my SQL knowledge
Current Table:
Date Project SubProject Check
01/08/2007 J I 72%
01/08/2007 L None 75%
01/08/2007 Sa E 51%
01/11/2007 F None 42%
01/11/2007 J I 66%
01/01/2008 Sk None 46%
01/03/2008 C None 70%
01/03/2008 F None 100%
01/03/2008 J I 83%
01/03/2008 L None 85%
01/03/2008 M 2 93%
01/03/2008 M 3 85%
01/03/2008 R M 68%
01/03/2008 Sa E1 56%
01/06/2008 Sa E2 80%
Project SubProject Check1 Check2 Check3
J I 72% 66% 83%
L None 75% 85% 0%
Sa E1 51% 56% 0%
F None 42% 100% 0%
Sk None 46% 0% 0%
C None 70% 0% 0%
M 2 93% 0% 0%
M 3 85% 0% 0%
R M 68% 0% 0%
Sa E2 80% 0% 0%
So what has happened is its grouped the project and subproject (GROUP
BY Project, SubProject)
and the lines that were lost by the grouping, it has added the score
into a new column (check2, check 3, ect.) if there was another J & I
Check then the new table would need an additional column (Check4).
So basically I need to add as many columns as there are Projects with
the same SubProject.
Also the checks need to appear in order of the dates, so check 1 is
the earliest.
Any help would be HUGELY appreciated as I just can't crack this one!
Many Thanks!!