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

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.

I hope that makes sense.

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. :slightly_smiling:

Create Table #Rank1 (
Rank INT,
StoreNum NVARCHAR(10),
StoreName NVARCHAR(50),
OwnSSN CHAR(9),
LName VCHAR(25),
FName VCHAR(25),
Share INT)

Create Table #Rank2 (
Rank INT,
StoreNum NVARCHAR(10),
StoreName NVARCHAR(50),
OwnSSN CHAR(9),
LName VCHAR(25),
FName VCHAR(25),
Share INT)

Insert Into #RANK1

SELECT tblTest.RANK, tblTest.StoreNum, tblTest.StoreName, tblTest.OwnSSN, tblTest.LName, tblTest.FName, tblTest.Share
FROM tblTest
WHERE (((tblTest.RANK)=1))

Same idea for populating #RANK2, Then pull out what you want:

SELECT #Rank1.RANK, #Rank1.StoreNum, #Rank1.StoreName, #Rank1.OwnSSN, #Rank1.LName, #Rank1.FName, #Rank1.Share, #Rank2.RANK, #Rank2.OwnSSN, #Rank2.LName, #Rank2.FName
FROM #Rank1 LEFT JOIN #Rank2 ON #Rank1.StoreNum = #Rank2.StoreNum

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?

That is why I tried the CTE approach.

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.

haha - it wouldn't be the first time I overthink something. I am by no means an expert either. I will give it a try and let you know.

Thanks again.

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

Create Table #tblTest (
Rank INT,
StoreNum NVARCHAR(10),
StoreName NVARCHAR(50),
OwnSSN CHAR(9),
LName VCHAR(25),
FName VCHAR(25),
Share INT)

GO

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.

Okay - got it. Thanks Craig.

Sometimes it is right there in front of your face and you can't see it.