使用动态sql和自定义列名称两次透视数据 [英] Pivoting Data twice with dynamic sql and custom column names

问题描述

我有一个简单的表格，其中包含非唯一的帐号，产品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和自定义列名称两次透视数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！