Row_Number() 需要动态枢轴 [英] Dynamic Pivot Needed with Row_Number()
问题描述
我使用的是 Microsoft SQL Server Management Studio 2008.
I am using Microsoft SQL Server Management Studio 2008.
我有这样的数据:
Client ID Value
-------------------------------
12345 Did Not Meet
12345 Did Not Meet
12345 Partially Met
12346 Partially Met
12346 Partially Met
12346 Partially Met
12347 Partially Met
12347 Partially Met
12347 Did Not Meet
12347 Met
我希望结果如下所示:
Client ID Value1 Value2 Value3 Value4
12345 Did Not Meet Did Not Meet Partially Met NULL
12346 Partially Met Partially Met Partially Met NULL
12347 Partially Met Partially Met Did Not Meet Met
列是未知的,所以我知道我需要一个动态查询.我尝试了一个带有枢轴函数的动态查询,但只在相同类型的值下得到了分组.所以聚合函数对我不利.
The columns are unknown so I know I need a dynamic query. I have tried a dynamic query with a pivot function but only got groupings under the same type of value. So the aggregate function worked against me.
这是我试过的查询:
Declare @Columns nvarchar(max);
Declare @DynamicPivotQuery nvarchar(max);
Select @Columns=
COALESCE(@Columns+',','')+QUOTENAME(Value)
from (select distinct Document.Value
from Document d
join Client c on d.clientid=c.id
)
as t1
Set @DynamicPivotQuery=
N'Select ClientID, ' + @Columns + '
from
(select Document.ClientID,
DocumentFact.Value,
from Document d
join Client c on d.clientid=c.id
) p
Pivot (max(Value) for Value in ('+@Columns+'))
as pivottable
order by ClientID;'
执行(@DynamicPivotQuery)
Exec (@DynamicPivotQuery)
接下来我添加了 row_number 和 partition 函数,但似乎无法调试.我得到的错误是:
Next I added the row_number and partition functions but can't seem to debug this. The error I get is:
消息 102,级别 15,状态 1,第 29 行
')' 附近的语法不正确.
Msg 102, Level 15, State 1, Line 29
Incorrect syntax near ')'.
哪个靠近 XML Path 函数.
Which is near the XML Path function.
对此的任何帮助将不胜感激.谢谢.
Any help on this would be appreciated. Thanks.
select @Columns=
COALESCE(@Columns+',','')+QUOTENAME(Value)
from (select distinct Document.Value
, 'name'+ CAST (row_number() over
(Partition BY clientid order by clientid) as NVARCHAR (10)) as Cols
from document d
join Clients c on d.clientid=c.id
t1
--FOR XML PATH('')), 1, 1, N'');
FOR XML PATH('')), TYPE).value('.','NVARCHAR(MAX)'),1,2,'')
order by ClientID
推荐答案
with using a cte
, with row_number
你可以得到结果:
with using a cte
, with row_number
you can achieve the result:
您的架构:
create table your_table([Client ID] int ,Value varchar(50));
insert into your_table values
(12345, 'Did Not Meet'),
(12345, 'Did Not Meet'),
(12345, 'Partially Met'),
(12346, 'Partially Met'),
(12346, 'Partially Met'),
(12346, 'Partially Met'),
(12347, 'Partially Met'),
(12347, 'Partially Met'),
(12347, 'Did Not Meet'),
(12347, 'Met');
查询:
with cte as
(
select [Client ID] ci,value,
row_number() over(partition by [Client ID] order by value) as rn
from your_table
)
select distinct ci as [Client ID],
(select ct.value from cte ct where ct.ci=cte.ci and ct.rn=1) value1,
(select ct.value from cte ct where ct.ci=cte.ci and ct.rn=2) value2,
(select ct.value from cte ct where ct.ci=cte.ci and ct.rn=3) value3,
(select ct.value from cte ct where ct.ci=cte.ci and ct.rn=4) value4
from cte
结果:
Client ID value1 value2 value3 value4
12345 Did Not Meet Did Not Meet Partially Met (null)
12346 Partially Met Partially Met Partially Met (null)
12347 Did Not Meet Met Partially Met Partially Met
这篇关于Row_Number() 需要动态枢轴的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!