Results 1 to 7 of 7

Thread: [RESOLVED] SQL Server - would this be a pivot or something else?

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Resolved [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.

  2. #2

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    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.

  3. #3
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    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

  4. #4

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    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.

  5. #5

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    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.

  6. #6

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    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.

  7. #7

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    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
  •  



Click Here to Expand Forum to Full Width