Row_Number（）需要动态枢轴

宋泽鹏

我正在使用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
这些列是未知的，所以我知道我需要一个动态查询。我已经尝试过使用数据透视功能进行动态查询，但只在相同类型的值下进行分组。所以聚合函数对我不利。
这是我尝试过的查询：
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）
接下来，我添加了row_number和partition函数，但似乎无法对其进行调试。我得到的错误是：

- J/ C) F3 d' a& E. A& X消息102，级别15，状态1，第29行
. A# y- n/ z' A5 f+ F2 m: h ‘）’附近的语法不正确。
. i) W5 ^$ j" y
靠近XML Path函数。
任何帮助，将不胜感激。谢谢。
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

解决方案:


            与一起使用cte，row_number您可以达到以下结果：
您的架构：
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（）需要动态枢轴

浏览过的版块

宋泽鹏 LV1