0

I have a large customer dataset and I'm looking to query it for analysis. Currently, I have a list of CustomerID's, the various account numbers associated with those customer id's as well as the balance of those accounts. I would like to have the accounts listed horizontally instead of vertically so that each customer has just one row.

Here is the table:

CustomerID AccountNumber Balance rn
1234 98765 $100 3
1234 52349 $300 1
1234 92347 $4500 2
5678 54321 $200 3
5678 21879 $500 1
5678 67978 $799 4
5678 23454 $200 2

What I'm trying to do is have the query pivot the accountnumber column into a new column with that account's balance next to it without me having to statically specify the number of columns and without titling the column after each account number since we have hundreds of thousands of customers who can have any number of accounts.

This is what im looking for:

CustomerID AccountNumber1 AccountNumber1_Balance AccountNumber2 AccountNumber2_Balance ...AccountNumberN ...AccountNumberN_Balance
1234 98765 $100 52349 $300 NULL NULL
5678 54321 $200 21879 $500 23454 $200

I've tried using the ROW_NUMBER() as a window function and then looked at the highest row_number but then im not sure how to make that dynamic. Here's my current query:

SELECT 
   [CustomerID]
   , [AccountNumber]
   , [Balance]
   , ROW_NUMBER() OVER (PARTITION BY OD.[CustomerID] ORDER BY OD.[AccountNumber]) AS rn

FROM Accounts AS OD

I'm thinking I'd need something like:

SELECT 
   [CustomerID]
   , CASE 
       WHEN rn = 1 THEN [AccountNumber] 
       WHEN rn > 1 THEN [AccountNumber]+'2' -- ??
       -- Here is where im stuck, how do i make a new column for each rn
   , [Account1_Balance]
   , [Account2_Balance]
   , ROW_NUMBER() OVER (PARTITION BY OD.[CustomerID] ORDER BY OD.[AccountNumber]) AS rn

FROM Accounts AS OD

I am using Windows 10 with the following items:

SQL Server Management Studio 19.0.20200.0+9286509b
SQL Server Management Objects (SMO) 16.200.48036.0+7d3b143d945e1aa638acdb02c0364e263d5ec973 Microsoft T-SQL Parser
16.0.22524.0+62eedb15cd3cde34e51c8fbbdf9b06e575ec912e Microsoft Analysis Services Client Tools 16.0.19970.0
Microsoft Data Access Components (MDAC) 10.0.19041.2604
Microsoft MSXML 3.0 6.0
Microsoft .NET Framework 4.0.30319.42000
Operating System 10.0.19045

The database is running on Windows Server 2019 Datacenter (10.0) and SQL Server 15.0.4236.7

Edit: The solutions posted in this thread: SQL Server dynamic PIVOT query?

don't help me because they use distinct categories within the column to create a columns variable to pivot. That is not a viable solution because then I would end up with thousands of columns for each account number. What I need help with is translating the highest row number and creating that many columns title [Account]rn so that each account has a place to be pivoted into.

user2383671
  • 1
  • 1
  • I agree that the PIVOT solutions in the linked "duplicate" answer cannot be used here, because you need to generate multiple columns per account. PIVOT cannot do that. If you haven't already come up with a solution since this was posted several weeks ago, I believe what you need is *conditional aggregation* to select appropriate values for each column. ... – T N Jun 20 '23 at 04:31
  • ... Start by assigning `ROW_NUMBER()` as you have already done. Next wrap that up as a subquery and `GROUP BY CustomerID`. Finally, define your columns using case expressions of the form `CASE WHEN rn = 1 THEN MAX(AccountNumber) END AS AccountNumber1, CASE WHEN rn = 1 THEN MAX(Balance) END AS Balance1` and repeat using `rn = 2`, `rn = 3`, etc up to the max anticipated. If there is no known upper bound, you will have to calculate the number of column groups based on the data and then build up a *dynamic SQL* query withe the necessary number of column groups. – T N Jun 20 '23 at 04:36

0 Answers0