数据透视表中的静态列名称 [英] static column name in pivot table

查看:93
本文介绍了数据透视表中的静态列名称的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)


select @cols = '[1015],[1060],[1261],[1373]'

print @cols



set @query = 'SELECT header, ' + @cols + ' 
              from 
             (
                select product_id, header, value
                from
                (
                  select 
                        cast(product_id  as varchar(100))product_id , 
                        cast(product_name  as varchar(100)) product_name,
                        cast(product_price as varchar(100)) product_price, 
                        cast(product_weight as  varchar(100))product_weight,
                        cast((select TOP 1  image_name from tblProductImage where tblProductImage.product_id=tblProduct.product_id) as  varchar(100)) ProductImage      
                  from tblProduct 

                ) p
                unpivot
                (
                  value
                  for header in (product_name, product_price, product_weight,ProductImage)
                ) unp
            ) x
            pivot 
            (
                max(value)
                for product_id in (' + @cols + ')
            ) p '

execute(@query)

我正在使用上面的查询及其生成的以下输出

I am using above query and its generating following output

现在我的问题是我希望列名应为静态..,因为当前最多有5列,其中第一列将是标题,这是可以的

Now my problem is i want that column name should be static .. as there currently there max to max 5 columns out of them first column will be header which is OK

但对于列的其余部分,我也希望名称类似于Prodcut1,Product2,Product3和Product4.如果只有三个产品,则它还会显示五列,但对于最后一列,所有属性值应为

but for rest of column's i want also there names should be like Prodcut1,Product2,Product3, and Product4 . In case if there only three products then its also shows five columns but for last column all attributes value should be null

推荐答案

如果我正确理解了您的问题,那么当您传递特定的product_id时,您希望列名称为Product1Product2等.

If I am understanding your question correctly then while you are passing in specific product_ids, you want the column names to be Product1, Product2, etc.

如果正确,则可以通过应用row_number()创建静态列名称.

If that is correct, then you can create the static column names by applying row_number().

您的代码将调整为以下内容:

Your code will be adjusted to the following:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

DECLARE @prods AS NVARCHAR(MAX)

select @prods = '141,142,143,144'

set @query = 'SELECT header, Product1, Product2, Product3, Product4
              from 
              (
                select header, value,
                  ''Product''+cast(row_number() over(partition by header 
                                                   order by header) as varchar(10)) prods
                from
                (
                  select 
                    cast(product_id as varchar(10)) product_id,
                    product_name, 
                    cast(product_price as varchar(10)) product_price, 
                    product_weight
                  from tblProduct
                  where product_id in ('+@prods+') 
                ) p
                unpivot
                (
                  value
                  for header in (product_name, product_price, product_weight, product_id)
                ) unp
            ) x
            pivot 
            (
                max(value)
                for prods in (Product1, Product2, Product3, Product4)
            ) p '

execute(@query)

请参见带演示的SQL提琴.结果如下:

|         HEADER | PRODUCT1 | PRODUCT2 | PRODUCT3 | PRODUCT4 |
--------------------------------------------------------------
|     product_id |      141 |      142 |      143 |      144 |
|   product_name |     A141 |     A144 |     A143 |     A142 |
|  product_price |      300 |     4000 |     5000 |      200 |
| product_weight |    200gm |    100gm |    400gm |    300gm |

这篇关于数据透视表中的静态列名称的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆