vendredi 8 janvier 2016

multiple column values on same row

I have a situation where I have to return owners of stores. Some have multiple owners and some do not. Rather than usual results:

>     RANK STORE# STORE NAME                 OWNER SS  LAST NAME   FIRST NAME OWNER SHARE
    >     1    123456 LUK OIL                    111223333 SMITH       JOHN       1
    >     1    987654 A LITTLE BIT OF EVERYTHING 222334444 JONES       WILLIAM    40
    >     2    987654 A LITTLE BIT OF EVERYTHING 333445555 DANIELS     SCOTT      60
    >     1    456789 GRIFFEY LIQUORS            444556666 GRIFFEY     KEN        51
    >     2    456789 GRIFFEY LIQUORS            555667777 PIAZZA      MIKE       49
    >     1    654321 QUICK STOP DELI            666778888 HALLADAY    ROY        100
    >     1    124578 WINE COUNTRY               777889999 FITZPATRICK RYAN       100
    >     1    895623 WAWA                       888990000 MARTIN      CURTIS     100

I need it to look like the following:

RANK    STORE#  STORE NAME               OWNER SS     LAST NAME FIRST NAMEOWNER SHARE OWNER SS2 LAST NAME2 FIRST NAME2  
1 123456    LUK OIL                          111223333    SMITH          JOHN      1
1 987654    A LITTLE BIT OF EVERYTHING       222334444    JONES          WILLIAM   40 333445555 DANIELS     SCOTT                        
1 456789    GRIFFEY LIQUORS                  444556666    GRIFFE         KEN       51 555667777 PIAZZA      MIKE
1 654321    QUICK STOP DELI                  666778888    HALLADAY       ROY       100
1 124578    WINE COUNTRY                     777889999    FITZPATRICK    RYAN      100
1 895623    WAWA                             888990000    MARTIN         CURTIS    100

I have tried using CTEs, and temp tables but I cannot filter the data by rank to put into two different tables before selecting. I am drawing a blank.

Aucun commentaire:

Enregistrer un commentaire