To see less ads Register or Login ----- Daily Fantasy Sports games 18+

Making raw data easy to read

A forum to discuss or share knowledge of spreadsheets.
Post Reply
bspittles
Dumbledore
Posts: 7607
Joined: 13 Oct 2005, 18:26
Location: In hiding

Making raw data easy to read

Post by bspittles »

I have 3 columns in a spreadsheet:

Code: Select all

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: Select all

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!

User avatar
Finsimbo
Grumpy Old Man
Posts: 2456
Joined: 13 Oct 2005, 18:27
Location: You tell me
FS Record: If only there was a record

Re: Making raw data easy to read

Post by Finsimbo »

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
Dumbledore
Posts: 7607
Joined: 13 Oct 2005, 18:26
Location: In hiding

Re: Making raw data easy to read

Post by bspittles »

It's in both Excel and an SQL database.

Please describe your straightforward idea!!

User avatar
Finsimbo
Grumpy Old Man
Posts: 2456
Joined: 13 Oct 2005, 18:27
Location: You tell me
FS Record: If only there was a record

Re: Making raw data easy to read

Post by Finsimbo »

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: Select all

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
Dumbledore
Posts: 7607
Joined: 13 Oct 2005, 18:26
Location: In hiding

Re: Making raw data easy to read

Post by bspittles »

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 all

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!

User avatar
eagle224
Grumpy Old Man
Posts: 2427
Joined: 10 Nov 2005, 22:14
Location: England
FS Record: Veteran

Re: Making raw data easy to read

Post by 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 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
Dumbledore
Posts: 7607
Joined: 13 Oct 2005, 18:26
Location: In hiding

Re: Making raw data easy to read

Post by bspittles »

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.

User avatar
Mystery
FISO Knight
Posts: 13816
Joined: 13 Oct 2005, 18:33
Location: Just about here
FS Record: 116th in TFF 05/06
Contact:

Making raw data easy to read

Post by Mystery »

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

User avatar
Finsimbo
Grumpy Old Man
Posts: 2456
Joined: 13 Oct 2005, 18:27
Location: You tell me
FS Record: If only there was a record

Re: Making raw data easy to read

Post by Finsimbo »

Code: Select all

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

User avatar
Ashers
FISO Knight
Posts: 19810
Joined: 21 Jul 2008, 17:31
Location: Stroud
FS Record: OFL Fantasy Fives Winner 2011/2012, SDT wins WDT, weekly, monthly and seasonal prizes. Fantasy Darts daily winner, TFFO mini league winner & FISO U21 Threes Champs 13.
Contact:

Re: Making raw data easy to read

Post by Ashers »

[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
Dumbledore
Posts: 7607
Joined: 13 Oct 2005, 18:26
Location: In hiding

Re: Making raw data easy to read

Post by bspittles »

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
Dumbledore
Posts: 7607
Joined: 13 Oct 2005, 18:26
Location: In hiding

Re: Making raw data easy to read

Post by bspittles »

Finsimbo wrote:

Code: Select all

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
Dumbledore
Posts: 7607
Joined: 13 Oct 2005, 18:26
Location: In hiding

Re: Making raw data easy to read

Post by bspittles »

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 all

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).

User avatar
Finsimbo
Grumpy Old Man
Posts: 2456
Joined: 13 Oct 2005, 18:27
Location: You tell me
FS Record: If only there was a record

Re: Making raw data easy to read

Post by Finsimbo »

Taken from here

http://www.sql-server-helper.com/faq/or ... s-p01.aspx" onclick="window.open(this.href);return false;

Code: Select all

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
Dumbledore
Posts: 7607
Joined: 13 Oct 2005, 18:26
Location: In hiding

Re: Making raw data easy to read

Post by bspittles »

Finsimbo wrote:Taken from here

http://www.sql-server-helper.com/faq/or ... s-p01.aspx" onclick="window.open(this.href);return false;

Code: Select all

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 all

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: Select all

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

User avatar
Finsimbo
Grumpy Old Man
Posts: 2456
Joined: 13 Oct 2005, 18:27
Location: You tell me
FS Record: If only there was a record

Re: Making raw data easy to read

Post by Finsimbo »

bspittles wrote:
Finsimbo wrote:Taken from here

http://www.sql-server-helper.com/faq/or ... s-p01.aspx" onclick="window.open(this.href);return false;

Code: Select all

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 all

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: Select all

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

User avatar
WilBert
Dumbledore
Posts: 7767
Joined: 28 Dec 2009, 21:07
Location: 2nd in SP4's Ashes comp.

Re: Making raw data easy to read

Post by WilBert »

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
Dumbledore
Posts: 7607
Joined: 13 Oct 2005, 18:26
Location: In hiding

Re: Making raw data easy to read

Post by bspittles »

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 :-)

View Latest: 1 Day View Your posts
Post Reply

Return to “Spreadsheets”