-
Dec 22nd, 2020, 07:35 AM
#1
Thread Starter
PowerPoster
[RESOLVED] SQL Server - would this be a pivot or something else?
Here are my query and results:
Code:
select pri.[customer_id], pri.[EmpID] as PrimaryRep, sec.EmpID as SecondaryRep
from EmpSalesReps pri
left join EmpSalesReps sec on sec.customer_id = pri.customer_id and sec.bPrimary = 0
where (pri.customer_id = '100014' or pri.customer_id = '102709' or pri.customer_id = '102706')
and pri.bPrimary = 1
Code:
customer_id PrimaryRep SecondaryRep
100014 29 18
100014 29 24
100014 29 98
102706 66 42
102709 66 NULL
I want make repeating customer rows into one row with n-columns for however many SecondaryReps there are:
Code:
customer_id PrimaryRep SecondaryRep SecondaryRep SecondaryRep
100014 29 18 24 98
102706 66 42 NULL NULL
102709 66 NULL NULL NULL
EmpsSalesReps is the table I am selecting from and it will have one row with the employee number of the customer's primary rep and and n-rows with employee numbers who are secondary reps, defined by bPrimary = 1 or 0. Is there a SQL construct that can manipulate this into what I want? Thank you. (Please excuse the terrible formatting. When I edit it, it lines up but when I post it, it does not. I hope it's readable).
Last edited by si_the_geek; Dec 22nd, 2020 at 08:19 AM.
Reason: added Code tags for values (technically wrong, but looks right!)
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Dec 22nd, 2020, 08:25 AM
#2
Thread Starter
PowerPoster
Re: SQL Server - would this be a pivot or something else?
It's kind of like this and this is actually what I did yesterday until my boss further explained what she wanted.
https://www.mssqltips.com/sqlservert...l-server-data/
But instead of the USR_NAMES column holding KEITH, STEFAN, EDUARD, BRAD concatenated, I want 4 columns with KEITH in one, STEFAN in the next, etc. And of course, it is not fixed at 4.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Dec 22nd, 2020, 09:19 AM
#3
Re: SQL Server - would this be a pivot or something else?
Is there a SQL construct that can manipulate this into what I want?
Yes. You're looking for a PIVOT.
The syntax can be a bit intimidating at first but take a punt and see how you get on. The query that you already have will be the source of the pivot (so it forms the FROM part of the query) and you then define the row values you want to flip into columns.
I can see a problem looming, though. The pivot statement requires a known number of columns, and I bet you have a variable number. So what you actually need is a dynamic pivot which, I'm afraid, TSQL doesn't natively support. Instead you need to construct the tsql statement using dynamic sql.
That's probably going to be too much to bite off in one chunk, though, so I suggest trying to do a static pivot with an arbitrary number of columns first. Once you're happy with that and the way it works, you can feed that principle into the dynamic pivot.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Dec 22nd, 2020, 09:22 AM
#4
Thread Starter
PowerPoster
Re: SQL Server - would this be a pivot or something else?
I've got it, thank you.
This is my model: https://stackoverflow.com/questions/...ltiple-columns
I will post my exact solution when I am done coding it.
Thanks @si_the_geek for the formatting!
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Dec 22nd, 2020, 09:23 AM
#5
Thread Starter
PowerPoster
Re: SQL Server - would this be a pivot or something else?
Sorry @FunkyDexter, I didn't refresh!
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Dec 22nd, 2020, 10:42 AM
#6
Thread Starter
PowerPoster
Re: SQL Server - would this be a pivot or something else?
Code:
select pri.[customer_id], pri.[EmpID] as PrimaryRep, sec.EmpID as SecondaryRep
into #tmpTest
from EmpSalesReps pri
left join EmpSalesReps sec on sec.customer_id = pri.customer_id and sec.bPrimary = 0
where (pri.customer_id = '100014' or pri.customer_id = '102709' or pri.customer_id = '102706')
and pri.bPrimary = 1
go
Declare @SQL varchar(max) = Stuff((Select Distinct ',' + QuoteName(concat('SecondaryRep ',RN))
From (Select Distinct RN=Row_Number() over (Partition By customer_id,PrimaryRep Order By [SecondaryRep])
From #tmpTest) A
Order by 1 For XML Path('')),1,1,'')
Select @SQL = '
Select [customer_id], PrimaryRep,' + @SQL + '
From (
Select customer_id,primaryrep,B.*
From (
Select *,RN=Row_Number() over (Partition By customer_id,primaryrep Order By [SecondaryRep]) From #tmpTest
) A
Cross Apply (Values (concat(''SecondaryRep '',A.RN),A.[SecondaryRep])
) B (Col,Value)
) A
Pivot (max(Value) For [Col] in (' + @SQL + ') ) p'
/*
select @SQL
Select [customer_id], PrimaryRep, [SecondaryRep 1],[SecondaryRep 2],[SecondaryRep 3] From ( Select customer_id,primaryrep,B.* From ( Select *,RN=Row_Number() over (Partition By customer_id,primaryrep Order By [SecondaryRep]) From #tmpTest ) A Cross Apply (Values (concat('SecondaryRep ',A.RN),A.[SecondaryRep]) ) B (Col,Value) ) A Pivot (max(Value) For [Col] in ([SecondaryRep 1],[SecondaryRep 2],[SecondaryRep 3]) ) p
*/
Exec(@SQL);
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Dec 22nd, 2020, 10:44 AM
#7
Thread Starter
PowerPoster
Re: [RESOLVED] SQL Server - would this be a pivot or something else?
Also, @FunkyDexter, thanks for the tips on how to approach it. I've done only a few of these. If I am lucky enough to find an example of what I want to do, I then find it pretty straightforward to adapt that to my own tables and columns.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|