如何在不了解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)
您可以使用 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种解决方案来解决
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)
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
- 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]
- 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屋!