问题描述
我有一个简单的表格,其中包含非唯一的帐号,产品ID和数量:
I have a simple table of non-unique account numbers, product IDs, and quantities:
例如:
account|productid|qty
1 100 1
1 100 1.5
1 102 6
2 100 1
我正在尝试将其动态地转换为这种结构:
I'm trying to get this to be pivoted dynamically into this kind of structure:
account|product1|qty1|product2|qty2|etc..|etc..
1 100 2.5 102 6 NULL NULL
2 100 1 NULL NULL NULL NULL
其中一些客户可能已经订购了数百种不同的产品,因此尝试硬编码商品最终成为不可能.
Some of these customers can have ordered hundreds of different products, so trying to hard-code things ended up being out of the question.
我设法将其转入一个表格,如
I've managed to pivot this into a table like
account|100|102
1 2.5 6
2 1 NULL
以产品ID作为列标题,
with product IDs as column headers,
具有:
DECLARE @sql AS NVARCHAR(4000)
, @col AS NVARCHAR(2000);
SELECT @col = ISNULL(@col + ', ', '') + QUOTENAME(x.productid)
FROM
(
SELECT DISTINCT
tp.productid
FROM purchases AS tp
) x
;
SET @sql
= N'SELECT * FROM purchases as p PIVOT ( SUM(qty) FOR [productid] IN (' + @col
+ ')) piv';
SELECT @sql;
EXEC sys.sp_executesql @sql;
我试图通过将我的select语句更改为以下内容来两次选择相同的列,以获取每列的数量和产品ID:
I attempted to select the same columns twice to get a qty and product ID for each, by changing my select statement to:
@coltest = ISNULL(@col + ', ', '') + (QUOTENAME(x.productid) + ', ' + QUOTENAME(x.productid))
但是抛出一个错误,即"piv"被多次指定了"productid".
however was thrown an error that 'productid was specified multiple times for 'piv'.'
将数据透视成两个单独的自定义命名增量列的最佳方法是什么?
What's the best way to approach pivoting into two seperate custom-named incrementing columns?
推荐答案
无需两次旋转.
示例
Declare @SQL varchar(max) = '
Select *
From (
Select A.Account
,B.*
From (Select Account
,ProductID
,Qty = sum(Qty)
,RN=Row_Number() over (Partition By Account Order by ProductID)
From YourTable
Group By Account,Productid
) A
Cross Apply (values (''qty''+cast(RN as varchar(25)),cast(Qty as varchar(100)))
,(''product''+cast(RN as varchar(25)),cast(productid as varchar(100)))
) B (Item,Value)
) A
Pivot (max([Value]) For [Item] in (' + Stuff((Select Distinct ','+QuoteName('product'+ColNr)
+','+QuoteName('qty'+ColNr)
From (Select Distinct ColNr=cast(Row_Number() over (Partition By Account,ProductID Order by (Select NULL)) as varchar(25)) From YourTable ) A
Order By 1
For XML Path('')),1,1,'') + ') ) p'
Exec(@SQL);
Print @SQL
返回
如果有助于可视化-子查询会生成
这篇关于使用动态sql和自定义列名称两次透视数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!