数据透视表中的静态列名称 [英] static column name in pivot table
问题描述
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时,您希望列名称为Product1
,Product2
等.
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屋!