数据透视表问题:原始表或枚举中没有标识符 [英] Pivot Table issue: no identifier in original table or enumeration
问题描述
我在使用数据透视表或动态表时遇到问题.
I'm having an issue with using pivot or dynamic tables.
我发现这个例子对理解我如何完成这项任务非常有帮助.但是,我缺少内部标识符列.
I found this example to be very helpful in understanding how I can accomplish this task; however, I am missing the inside identifier column.
上面的帖子中提供了一个SQL提琴: http://www .sqlfiddle.com/#!3/7fad2/6
There is an SQL fiddle provided here in the post above: http://www.sqlfiddle.com/#!3/7fad2/6
您可以看到在第二个表propertyObjects中,每个objectID都有一个计数1、2、3、4.我没有那个propertyID计数.这就是我所有的
You can see that in the second table propertyObjects, there is a count 1, 2, 3, 4 for each objectID. I do not have that propertyID count. This is all I have
case category
1 xx
1 xyx
1 abc
2 ghj
2 asdf
3 dfgh
如您所见,每种情况下我都有许多不同的类别,但是没有类别标识符字段.
As you can see I have a number of different categories for each case, but no category identifier field.
这就是我需要的:
case cat1 cat2 cat3
1 xx xyx abc
2 ghj asdf
3 dfgh
所以我想我可能需要在源表中添加一列,并以某种方式枚举每种情况下的类别.这将使我可以在提供的示例中使用枢轴.有什么想法吗?
So I am thinking I might need to add a column to the source table and somehow enumerate the categories per case. This would make it possible for me to use the pivot in the provided example. Thoughts?
我尝试使用row_number
来完成此操作,但它不会在每种情况下都停止,它只是继续对整个表进行计数.
I tried to use row_number
to accomplish this, but it does not stop at each case number, it just continues on counting the entire table.
推荐答案
由于每个case
具有多个值,因此您将需要使用row_number()
获取每个类别的单独列.
Since you have multiple values for each case
, then you will need to use row_number()
to get the separate columns for each category.
在编写动态SQL版本之前,我将首先编写一个硬编码版本.该代码将类似于:
Before you write the dynamic SQL version I would first write a hard-coded version. The code will be similar to:
SELECT [case], cat1, cat2, cat3
FROM
(
SELECT [case], category,
'cat'+
cast(row_number() over(partition by [case]
order by category) as varchar(10)) seq
FROM yourTable
) x
PIVOT
(
max(category)
for seq in (cat1, cat2, cat3)
)p;
请参见带演示的SQL提琴.
现在您有了逻辑,然后可以将其转换为动态SQL:
Now you have the logic down, then you can convert it to dynamic SQL:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME('cat'+cast(seq as varchar(10)))
from
(
select row_number() over(partition by [case]
order by category) seq
from yourtable
) d
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT [case],' + @cols + '
from
(
SELECT [case], category,
''cat''+
cast(row_number() over(partition by [case]
order by category) as varchar(10)) seq
FROM yourTable
) x
pivot
(
max(category)
for seq in (' + @cols + ')
) p '
execute sp_executesql @query;
请参见带演示的SQL小提琴.这将为您提供结果:
See SQL Fiddle with Demo. This will give you the result:
| CASE | CAT1 | CAT2 | CAT3 |
|------|------|--------|--------|
| 1 | abc | xx | xyx |
| 2 | asdf | ghj | (null) |
| 3 | dfgh | (null) | (null) |
这篇关于数据透视表问题:原始表或枚举中没有标识符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!