Forum Stats

  • 3,854,952 Users
  • 2,264,439 Discussions
  • 7,905,846 Comments

Discussions

A query to flaten, denormalize data.

s_dot1985
s_dot1985 Member Posts: 37
edited Aug 25, 2009 5:49PM in SQL & PL/SQL
Hi,

I'm not too sure whats the best way to go about this query. Hopefully somebody can point me in the right direction.

I have two tables Person & Contact_No.

Person:

PersonID
Name

Sample Data:
1 John
2 Mary
3 Jeff
4 Ann
etc

Contact_No:

PersonID
Contact_Type
Number

Sample Data:
1 Cell 12345678
1 Home 98765432
1 Cell2 12458788
2 Home 23245453
3 Cell 11233434
3 Home 12546342
3 Home2 65445645

There is a set number of types; Cell, Cell2, Home, Home2.

The reltationship between the tables is One Person to ZeroMany Contact_No.

The query i want to run will have the following columns:

PersonID___Cell____Cell2____Home___Home2
1_________456456________45646_________
2_______________________987897________
3_________454210________132131__231300
4_____________________________________

The query should return all persons no matter if they have a contact no or not. The columns will contain all the 4 types.

Any ideas?

Thanks,
Sean
Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,506 Red Diamond
    edited Aug 24, 2009 5:05PM Answer ✓
    Hi, Sean,

    That's called a pivot. You want to pivot, or rotate, the rows (up to 4 for each personid) so that they appear as 4 different columns.

    Here's one way to do that:
    SELECT	  p.personid
    ,	  MIN (CASE WHEN c.contact_type = 'Cell'  THEN num END)	AS cell
    ,	  MIN (CASE WHEN c.contact_type = 'Cell2' THEN num END)	AS cell2
    ,	  MIN (CASE WHEN c.contact_type = 'Home'  THEN num END)	AS home
    ,	  MIN (CASE WHEN c.contact_type = 'Home2' THEN num END)	AS home2
    FROM	      	 person    	p
    LEFT OUTER JOIN	 contact_no	c	ON	p.personid	= c.personid
    GROUP BY  p.personid
    ;
    If there are two (or more) of the same type of contact for the same personid, only the first one will be displayed. (That's what MIN is doing, above.)

    It looks like you went to some effort to make the output nice and readable. Thanks! That's very helpful.
    Here's how you can save some time and effort in the future. Type these 6 characters:
    (small letters only, inside curly brackets) before and after sections of formatted text, to preserve spacing.  Tabs and multiple spaces between the tags will not be compressed.  You can cut and paste things right out of your text editor or SQL*Plus window.  That's how I posted the query, above.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,506 Red Diamond
    edited Aug 24, 2009 5:05PM Answer ✓
    Hi, Sean,

    That's called a pivot. You want to pivot, or rotate, the rows (up to 4 for each personid) so that they appear as 4 different columns.

    Here's one way to do that:
    SELECT	  p.personid
    ,	  MIN (CASE WHEN c.contact_type = 'Cell'  THEN num END)	AS cell
    ,	  MIN (CASE WHEN c.contact_type = 'Cell2' THEN num END)	AS cell2
    ,	  MIN (CASE WHEN c.contact_type = 'Home'  THEN num END)	AS home
    ,	  MIN (CASE WHEN c.contact_type = 'Home2' THEN num END)	AS home2
    FROM	      	 person    	p
    LEFT OUTER JOIN	 contact_no	c	ON	p.personid	= c.personid
    GROUP BY  p.personid
    ;
    If there are two (or more) of the same type of contact for the same personid, only the first one will be displayed. (That's what MIN is doing, above.)

    It looks like you went to some effort to make the output nice and readable. Thanks! That's very helpful.
    Here's how you can save some time and effort in the future. Type these 6 characters:
    (small letters only, inside curly brackets) before and after sections of formatted text, to preserve spacing.  Tabs and multiple spaces between the tags will not be compressed.  You can cut and paste things right out of your text editor or SQL*Plus window.  That's how I posted the query, above.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • GVR
    GVR Member Posts: 439
    edited Aug 24, 2009 5:07PM
    You can try using
    with t as(
    select 1 pid,'Cell' ctype ,12345678 num from dual union all
    select 1 ,'Home', 98765432 from dual union all
    select 1 ,'Cell2' ,12458788 from dual union all
    select 2, 'Home' ,23245453 from dual union all
    select 3, 'Cell' ,11233434 from dual union all
    select 3, 'Home' ,12546342 from dual union all
    select 3, 'Home2', 65445645 from dual )
    select pid p_id , 
    max(decode (ctype,'Cell',num,null)) cell1,max( decode (ctype,'Cell2',num,null)) cell2,
    max(decode (ctype,'Home',num,null)) home1, max(decode (ctype,'Home2',num,null))home2 from t
    group by pid;
    Edited by: GVR on Aug 24, 2009 2:07 PM
  • damorgan
    damorgan Member Posts: 14,464 Bronze Crown
    edited Aug 24, 2009 5:12PM
    I have come to really hate the common-table expression examples posted here in the group as they almost always, as does this one, use hard-coded data.

    If you can not write it as a dynamic SELECT statement based on data contained in the underlying table of what value is it?

    If you are going to hard-code the values ... why not use the same amount of effort and just hard-code the result?

    To the OP: Is this school work? If not in what version and edition of Oracle are you working ... it sure looks like a homework assignment to me.
  • s_dot1985
    s_dot1985 Member Posts: 37
    Thanks Frank that was more than helpful!

    This is not homework! I just simplified the example so i could post it and explain it.

    I am querying in 10g. The real Contact_No table has 1.7 m rows and the 8 contact types, so the query takes nearly 6 mins to run. Any tips how i can speed up performance? Those min functions really slow it down.
  • damorgan
    damorgan Member Posts: 14,464 Bronze Crown
    edited Aug 25, 2009 5:49PM
    It strikes me, no explain plan report in sight, you are probably forcing a full table scan ... consider whether a function based index might help.
This discussion has been closed.