Making raw data easy to read

bspittles · **Posted:** 26 May 2011, 10:41

I have 3 columns in a spreadsheet:

Code:

Student ID   Course   Block
001          ENG      A
001          FRE      B
001          GER      D
002          GEO      A
002          GER      D
002          FRE      D
002          ENG      E
  etc

It needs to be shown as follows:

Code:

Student ID   A     B     C     D     E     F     G     H
001          ENG   FRE         GER
002          GEO               GER   ENG
002                            FRE

or maybe

Student ID   A     B     C     D         E     F     G     H
001          ENG   FRE         GER
002          GEO               GER/FRE   ENG

Any ideas?

Blocks are A to H, but not every block will be used for each student, and some blocks are used more than once.

The data has been extracted using SQL, so if there's a better way of formatting the raw data that way, I'm open to suggestions!

Finsimbo · **Posted:** 26 May 2011, 10:47

Is this in Excel or can it be in another tool?

If this was in a database table this would be quite straight forward to do.

bspittles · **Posted:** 26 May 2011, 10:59

It's in both Excel and an SQL database.

Please describe your straightforward idea!!

Finsimbo · **Posted:** 26 May 2011, 12:34

In sql could you just create a view of the base table something like

create view student_grades (stu_id, block_a,block_b,block_c,block_d,block_e,block_f)
as
select stu_id,subject from your_tab order by stu_id.

I did it by loading a table to achieve what you want but it could be changed into a view.

Code:

create table stu_grades(
Stu_id number(10),
A    Varchar2(5 CHAR),
B    varchar2(5 CHAR),
C   varchar2(5 CHAR),
D   varchar2(5 CHAR),
E   varchar2(5 CHAR),
F   varchar2(5 CHAR),
  9  G   varchar2(5 CHAR));

SQL> insert into stu_grades values (001,null,null,'Eng',null,null,null,'Ger');

1 row created.

SQL> commit;

SQL> select * from stu_grades;

    STU_ID A     B     C     D     E     F     G
---------- ----- ----- ----- ----- ----- ----- -----
         1             Eng                     Ger

bspittles · **Posted:** 26 May 2011, 13:37

But that's just inserting into a table of the required output format :-)

The SQL to extract the data from the existing tables is

Code:

select e.student_id, e.subject, b.block
from enrol as e
  inner join groupmember as gm on
    gm.subject = e.subject and
    gm.student_id = e.student_id
  inner join block as b on 
    b.group_index = gm.group_index

The enrol table contains the student_id and the subject, the groupmember table links the student_id and subject to the enrol table and the index to the block table (the group_index fields refer to a group table that doesn't need to be referenced.

That SQL produces the raw data described in my first post. If there's a way of outputting it into the required format without going into Excel, that would be great. I also have Visual Studio, but I can't see a way of formatting a report using that either.

I hope all that makes more sense!

eagle224 · **Posted:** 26 May 2011, 13:43

You could get something similar with a Pivot Table.
In Excel 2003 this would be done as follows ...
Select all your data
Data->PivotTable ... Wizard
Select Excel data
Select PivotTable
Click Next
Confirm the data range, including headings
Select New Worksheet and click layout
Drag Student and Course to ROW
Drag Grade to COLUMN
Drag Course to DATA
Click Options and untick Grand Totals for Rows
Click Finish

This will group data together by student and put the number of grades that got in Totals and flag which ones/grades they got.

It's not quite what you are trying to achieve, which I suspect would need some code behind it, but it comes close.

bspittles · **Posted:** 26 May 2011, 13:45

eagle224 wrote:

You could get something similar with a Pivot Table.
In Excel 2003 this would be done as follows ...
Select all your data
Data->PivotTable ... Wizard
Select Excel data
Select PivotTable
Click Next
Confirm the data range, including headings
Select New Worksheet and click layout
Drag Student and Course to ROW
Drag Grade to COLUMN
Drag Course to DATA
Click Options and untick Grand Totals for Rows
Click Finish

This will group data together by student and put the number of grades that got in Totals and flag which ones/grades they got.

It's not quite what you are trying to achieve, which I suspect would need some code behind it, but it comes close.

Thanks eagle, but I'm not after a count of the subjects, just the actual names. The "blocks" I refer to are timetable blocks rather than exam results. Sorry for the confusion.

Mystery · **Posted:** 26 May 2011, 22:16

In Access you'd just pivot query by code and student. This must need SQL because it seems like an Outerjoin query to me. If you have Access then you're grand. Just set the relationships to be right (one to many prolly) and you will be sound

Finsimbo · **Posted:** 27 May 2011, 07:40

Code:

create table stu(
Student_id       number(10),
Course         varchar2(5),
Block         varchar2(1));

insert done;

SQL> select * from stu;

STUDENT_ID COURS B
---------- ----- -
         1 ENG   A
         1 GER   B
         2 GER   E
SQL> select student_id, 
    max(decode(block,'A',course,null)) A,
    max(decode(block,'B',course,null)) B,
    max(decode(block,'C',course,null)) C,
    max(decode(block,'D',course,null)) D,
    max(decode(block,'E',course,null)) E,
    max(decode(block,'F',course,null)) F,
    max(decode(block,'G',course,null)) G
           from (select student_id,block, course
                   from stu)
  group by student_id;

STUDENT_ID A     B     C     D     E     F     G
---------- ----- ----- ----- ----- ----- ----- -----
         1       ENG   GER
         2                                    GER

Its lost a bit of its formatting

HTH

Ashers · **Posted:** 27 May 2011, 07:47

[quote="eagle224"]You could get something similar with a Pivot Table.
In Excel 2003 this would be done as follows ...
Select all your data
Data->PivotTable ... Wizard
Select Excel data
Select PivotTable
Click Next
Confirm the data range, including headings
Select New Worksheet and click layout
Drag Student ROW
Drag Block to COLUMN
Drag Course to DATA
Click Options and untick Grand Totals for Rows
Click Finish

Does that work?

bspittles · **Posted:** 27 May 2011, 10:21

Ashers wrote:

You could get something similar with a Pivot Table.
In Excel 2003 this would be done as follows ...
Select all your data
Data->PivotTable ... Wizard
Select Excel data
Select PivotTable
Click Next
Confirm the data range, including headings
Select New Worksheet and click layout
Drag Student ROW
Drag Block to COLUMN
Drag Course to DATA
Click Options and untick Grand Totals for Rows
Click Finish

Does that work?

I don't think it will, because the "Drag Course to DATA" part means it will count, sum or some other kind of summary. I need it to just display the values.

bspittles · **Posted:** 27 May 2011, 10:22

Finsimbo wrote:

Code:

create table stu(
Student_id       number(10),
Course         varchar2(5),
Block         varchar2(1));

insert done;

SQL> select * from stu;

STUDENT_ID COURS B
---------- ----- -
         1 ENG   A
         1 GER   B
         2 GER   E
SQL> select student_id, 
    max(decode(block,'A',course,null)) A,
    max(decode(block,'B',course,null)) B,
    max(decode(block,'C',course,null)) C,
    max(decode(block,'D',course,null)) D,
    max(decode(block,'E',course,null)) E,
    max(decode(block,'F',course,null)) F,
    max(decode(block,'G',course,null)) G
           from (select student_id,block, course
                   from stu)
  group by student_id;

STUDENT_ID A     B     C     D     E     F     G
---------- ----- ----- ----- ----- ----- ----- -----
         1       ENG   GER
         2                                    GER

Its lost a bit of its formatting

HTH

That looks very interesting... I'll give it a spin!

bspittles · **Posted:** 27 May 2011, 10:44

Unfortunately, DECODE is oracle only, and I'm using MS SQL Server :-(

I've tried using CASE, which looked similar but then each course had it's own record:

Code:

select  student_id,
   case block when 'A' then course else null end as A,
   case block when 'B' then course else null end as B,
   case block when 'C' then course else null end as C,
   case block when 'D' then course else null end as D,
   case block when 'E' then course else null end as E,
   case block when 'F' then course else null end as F,
   case block when 'G' then course else null end as G

from (select student_id,
             block, 
             course
     from stu)

group by student_id,
    block,
    course

which produces multiple records per student_id (one record per course).

Finsimbo · **Posted:** 27 May 2011, 11:03

Taken from here

http://www.sql-server-helper.com/faq/or ... s-p01.aspx

Code:

CASE input_expression
    WHEN when_expression_1 THEN result_expression_1
  [ WHEN when_expression_2 THEN result_expression_2 
    WHEN when_expression_n THEN result_expression_n ]
  [ ELSE else_result_expression ]
END

bspittles · **Posted:** 27 May 2011, 11:11

Finsimbo wrote:

Taken from here

http://www.sql-server-helper.com/faq/or ... s-p01.aspx

Code:

CASE input_expression
    WHEN when_expression_1 THEN result_expression_1
  [ WHEN when_expression_2 THEN result_expression_2 
    WHEN when_expression_n THEN result_expression_n ]
  [ ELSE else_result_expression ]
END

But then it doesn't put the results in separate columns in the way that your DECODE example did:

Code:

select  student_id,
   case block 
    when 'A' then course 
    when 'B' then course 
    when 'C' then course 
    when 'D' then course 
    when 'E' then course 
    when 'F' then course 
    when 'G' then course 
    else null 
   end

from (select student_id,
             block, 
             course
     from stu)

group by student_id,
    block,
    course

produces:

Code:

STUDENT_ID COURS
---------- ----- -
         1 ENG  
         1 GER  
         2 GER  

Finsimbo · **Posted:** 27 May 2011, 11:26

bspittles wrote:

Finsimbo wrote:

Taken from here

http://www.sql-server-helper.com/faq/or ... s-p01.aspx

Code:

CASE input_expression
    WHEN when_expression_1 THEN result_expression_1
  [ WHEN when_expression_2 THEN result_expression_2 
    WHEN when_expression_n THEN result_expression_n ]
  [ ELSE else_result_expression ]
END

But then it doesn't put the results in separate columns in the way that your DECODE example did:

Code:

select  student_id,
   case block 
    when 'A' then course 
    when 'B' then course 
    when 'C' then course 
    when 'D' then course 
    when 'E' then course 
    when 'F' then course 
    when 'G' then course 
    else null 
   end

from (select student_id,
             block, 
             course
     from stu)

group by student_id,
    block,
    course

produces:

Code:

STUDENT_ID COURS
---------- ----- -
         1 ENG  
         1 GER  
         2 GER  

Ah well, I don't have a test sql server to try on. Best I can do

WilBert · **Posted:** 27 May 2011, 11:42

Looks like you're near to an answer.

If it doesn't work I'm sure Excel could deliver a result by a simple solution (but mine are often convoluted)

e.g. by turning the combination into values - (e.g. Student = 1, 2, 3, 4, Course = 1000, 2000, 3000 etc and block = .1, .2, .3, .4 etc ) giving a unique for each possible combination then getting your display table to work out the value of each cell based on a grid.

Another potion would be a vbasic macro.

bspittles · **Posted:** 27 May 2011, 11:56

Finsimbo wrote:

Ah well, I don't have a test sql server to try on. Best I can do

Your help was very much appreciated! The DECODE was a new one on me, so I've learned something. Thanks for yout time and effort :-)

Making raw data easy to read

Who is online