如何在不了解T-SQL中固定列的情况下进行数据透视 [英] How to pivot without knowing fixed columns in T-SQL

问题描述

我有一个要调用的名为balance的表，但是这很困难，因为列名将分别标记为1,2,3，并且余额将按每个客户的降序进行排序.

这是当前表的屏幕截图:

和数据透视表:

我查看了其他视频并发布，但没有找到适合我当前情况的解决方案.因此，最终结果将是客户将按升序排序，余额将按降序排序.因此，对于客户3，最高余额500将放置在第1列，第2列中的300，第3列中的250.

用于创建示例数据的脚本:

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)

解决方案

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

这篇关于如何在不了解T-SQL中固定列的情况下进行数据透视的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！