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