db上的演示; >小提琴

您可以使用 ROW_NUMBER() 标记值的数量，例如:1, 2, 3.

请注意:ORDER BY [Balance] DESC可以根据需要获取生成的值.

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;

输出

已更新

由于连接字符串没有记录，也不可靠.它并不总是能按预期运行.因此，您应该使用以下2种解决方案来解决

1. 使用
2. 使用 XML扩展

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],

感谢@GarethD的评论.在 db<>小提琴

上进行检查

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)

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