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
What is the process/SQL that creates the original (current) dataset? Depending on how the data is actually stored this could be simple or more of a code based solution.
select rank() over(partition by m.agent_number order by m.agent_number, ss_no) as ranknum, m.agent_number, trade_name, ss_no, last_name_owner, first_name_owner, share_pct
from owner o inner join main m on o.agent_number = m.agent_number
where m.agent_number in (*list agent_number*) and
share_pct > 0
I put the rank in to try and differentiate between the first and second (or potentially more) owner. I also tried creating CTEs and then filtering on the rank but since there are some records with a rank of "1" that I need because there is no "2" I cannot exclude them all. In the second CTE I used case statements to display the second SS and name columns as NULL if SS2 is equal to SS.
"ss_no2" = (CASE
WHEN cte2.ss_no = cte1.ss_no THEN NULL
ELSE cte2.ss_no
END),
"last_name_owner2" = (CASE
WHEN cte2.ss_no = cte1.ss_no THEN NULL
ELSE cte2.last_name_owner
END),
"first_name_owner2" = (CASE
WHEN cte2.ss_no = cte1.ss_no THEN NULL
ELSE cte2.first_name_owner
END)
When doing that I still get two rows for the store if they have multiple owners. The first row will have the first owner and NULL for the second and the second row will have the first and second owner in the same row the way I want and showed in the OP. I am just not sure how to filter out the one with the duplicate store number and only one owner.
Makes sense, thanks. Here is how I would likely handle it- It's probably not the best way, but it is reliable and simple. tblTest is either the results of your original SQL or simply an additional temp table. Just remember to drop them all when you are done with them.
Thanks Craig for taking the time to answer.
In regard to your insert select statement, Rank is not a column in the table. I just used the rank function.
I don't think I can use it in the where clause, can I?
You might be over thinking this. Just create a new temp table with the results of your original SQL, which is where the rank number is generated, and go from there. Unless I'm missing something, that should be all you need to do. If I am missing something, feel free to point it out, I certainly don't consider myself an expert.
So with this I am getting the same results as using the CTEs. As I see it the problem is that I cannot filter the results of my insert select query by rank#. Rank is not allowed in a where clause. Using this method I think I would need to write rank1 to one table and rank2 to the other and then the query should be easy. but without being able to do that I get similar results I had from the CTE before my case statements.
I only included the first couple of stores as an example of each type of output.
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 111223333 SMITH JOHN
1 987654 A LITTLE BIT OF EVERYTHING 222334444 JONES WILLIAM 40 222334444JONES WILLIAM
1 987654 A LITTLE BIT OF EVERYTHING 222334444 JONES WILLIAM 40 333445555 DANIELS SCOTT
1 987654 A LITTLE BIT OF EVERYTHING 333445555 DANIELS SCOTT 60 333445555 DANIELS SCOTT
1 987654 A LITTLE BIT OF EVERYTHING 333445555 DANIELS SCOTT 60 222334444JONES WILLIAM
Insert Into #tblTest
select rank() over(partition by m.agent_number order by m.agent_number, ss_no) as ranknum, m.agent_number, trade_name, ss_no, last_name_owner, first_name_owner, share_pct
from owner o inner join main m on o.agent_number = m.agent_number
where m.agent_number in (list agent_number) and
share_pct > 0
If the SQL you posted above is actually what you used to generate your dataset, this will work. This is what I meant by putting the results of your original SQL into a temp table. After that you can run the rest of the creates and selects posted yesterday and get your data. Once the Rank is in a temp table it can be used in a where clause. It's just data at that point.