sql server - How to pivot without knowing fixed columns in T-SQL

Question

I have a table called balance which I wish to pivot, however it is quite difficult since the column names would be labelled 1,2,3 and balances would be sorted by descending order per customer.

This is screenshot of current table:

And the pivot table:

I reviewed other videos and post but I didn't find a solution to match my current situation what I want to achieve. So the final results would be the customer would be sorted by asc and balances would be sorted by desc. So for customer 3 the highest balance of 500 would be placed in column 1, 300 in column 2 and in 250 in column 3.

Script to create sample data:

select Customer, Balance
into #a
from (
values
  (1,     250), 
  (2,     500), 
  (1,     205), 
  (2,     600), 
  (2,     700),
  (3,     300),
  (3,     500),
  (3,     250)
) v (Customer, Balance)

Answer 1

Demo on db<>fiddle

You can use ROW_NUMBER() to mark the number of values, e.g: 1, 2, 3.

Note that: ORDER BY [Balance] DESC to get the generated value as you wish.

DECLARE 
    @columns NVARCHAR(MAX) = '',
    @sql     NVARCHAR(MAX) = '';


 SELECT Customer, Balance, Col = ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY [Balance] DESC)
 into #b
 FROM #a

SELECT @columns += QUOTENAME(Col) + ','
from (SELECT DISTINCT Col FROM #b) A

-- remove the last comma
SET @columns = LEFT(@columns, LEN(@columns) - 1);


SET @sql = 'SELECT * FROM ( SELECT Customer, Balance, Col FROM  #b) src PIVOT( MAX([Balance]) FOR Col IN ('+ @columns +')) AS pivot_table;';

-- execute the dynamic SQL
EXECUTE sp_executesql @sql;

Output

Updated

Since concatenating strings is undocumented and unreliable. It does not always work as expected. So you should resolve with 2 solutions below

1. Use STRING_AGG (From SQL Server 2017 and late)

SELECT STRING_AGG(QUOTENAME(Col), ', ')
from (SELECT DISTINCT Col FROM #b) A
// Output: [1], [2], [3]

2. Use XML Extensions

DECLARE  @columns NVARCHAR(MAX) = ''
SELECT @columns = (
  SELECT QUOTENAME(Col) + ', '
  FROM (SELECT DISTINCT Col FROM #b) A

  FOR XML PATH(''), TYPE
                   ).value('.','varchar(max)')
SELECT @columns 
// Output: [1], [2], [3],

Thanks @GarethD's comment. Check it out on db<>fiddle