Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Query / Pivot table question

Discussion in 'Information Technology' started by mmohon@gmail.com, Jul 28, 2006.

  1. mmohon@gmail.com

    mmohon@gmail.com
    Expand Collapse
    Guest

    I have a table that looks like this:

    patientNo diagNo diagCode
    ------------------------------------------------
    1 dx1 2500
    1 dx2 V358
    2 dx1 4010


    I want to rearrange it to look more like:

    patientNo dx1 dx2
    -------------------------------------------------
    1 2500 V358
    2 4010 (blank)

    Not all patients have the same number of DiagNo's occuring, one might
    have 3, one might have 23.

    So far I have this SQL statement

    SELECT patientNo, diagCode as DX1
    FROM mytable
    WHERE diagNo="DX1"

    I figured it would be followed by

    UNION ALL
    SELECT diagCode as DX2
    FROM mytable
    WHERE diagNo="DX2"

    but that doesn't work.
    anyone got any suggestions for me??
     
  2. Loading...

    Similar Threads Forum Date
    Query about Jhatka Meat by Shooting in Head Sikh Sikhi Sikhism Aug 26, 2011
    Who is a sikh? A non sikh friend's query!! Sikh Sikhi Sikhism Apr 30, 2010
    General Query Hard Talk Sep 4, 2008
    Power of pauri's in Japji Sahib query Sikh Sikhi Sikhism Aug 17, 2006
    Sikhism a query Book Reviews & Editorials Aug 2, 2005

  3. David Cressey

    David Cressey
    Expand Collapse
    Guest

    <mmohon@gmail.com> wrote in message
    news:1153237294.263347.242960@b28g2000cwb.googlegroups.com...
    > I have a table that looks like this:
    >
    > patientNo diagNo diagCode
    > ------------------------------------------------
    > 1 dx1 2500
    > 1 dx2 V358
    > 2 dx1 4010
    >
    >
    > I want to rearrange it to look more like:
    >
    > patientNo dx1 dx2
    > -------------------------------------------------
    > 1 2500 V358
    > 2 4010 (blank)
    >
    > Not all patients have the same number of DiagNo's occuring, one might
    > have 3, one might have 23.
    >
    > So far I have this SQL statement
    >
    > SELECT patientNo, diagCode as DX1
    > FROM mytable
    > WHERE diagNo="DX1"
    >
    > I figured it would be followed by
    >
    > UNION ALL
    > SELECT diagCode as DX2
    > FROM mytable
    > WHERE diagNo="DX2"
    >
    > but that doesn't work.
    > anyone got any suggestions for me??
    >


    Try using the "crosstab query wizard". A crosstab query in Access is like a
    pivot table in Excel.
     
  4. mmohon@gmail.com

    mmohon@gmail.com
    Expand Collapse
    Guest

    Sweet, that worked like a charm
     

Share This Page