在SQL中使用Pivot动态检索未正确显示在中的多行

Question

我希望为一个特定的数据透视表添加多行。

表名：MasterTable

FieldName | FieldValue | RowOrder
-----------------------------------
Field1    | F1value1   | 0
Field2    | F2value1   | 0
Field3    | F3value1   | 0
Field1    | F1value2   | 1
Field2    | F2value2   | 1
Field3    | F3value2   | 1

预期结果：

Field1     | Field2    | Field3  | RowOrder
--------------------------------------------
F1value1   | F2value1 | F3value1 | 0
F1value2   | F2value2 | F3value2 | 1

我试过这个代码

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(FieldName) from MasterTable where CatogoryId = @CatogoryId and CompanyId= @CompanyId FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')
set @query = 'SELECT  ' + @cols + ' from (select FieldName, FieldValue,
row_number() over (partition by FieldName order by FieldName) as seqnum from MasterTable where 
CatogoryId =  ('+  CONVERT(varchar(max),@CatogoryId) +') and CompanyId = ('+  CONVERT(varchar(max),@CompanyId) +') ) x
pivot 
(
max(FieldValue)
for FieldName in (' + @cols + ')
) p '
execute(@query)

我得到的输出是

Field1     | Field2    | Field3
---------------------------------
F1value1   | F2value2 | F3value1
F1value2   | F2value1 | F3value2

我发现在显示值时出现了问题。它显示出不同的顺序。在第1列第1行中显示"F1Value1"，然后在第1栏第2行显示"F1Value 2"，但在第2列第1列中显示"F2Value2"，第2栏第2列显示"F2Value 1"。我需要所有的"Value1"作为第一行，"value2"作为第二行。为此，我还在表"RowOrder"中添加了一个新字段，它将指定数据透视表的顺序。但我找不到解决办法。请帮助

Answer 1

您不需要任何seqnum。尝试以下操作：

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(FieldName) from MasterTable where CatogoryId = @CatogoryId and CompanyId= @CompanyId FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')
set @query = 'SELECT  ' + @cols + ', RowOrder
from 
(
select FieldName, FieldValue, RowOrder
from MasterTable where 
CatogoryId =  ('+  CONVERT(varchar(max),@CatogoryId) +') and CompanyId = ('+  CONVERT(varchar(max),@CompanyId) +')
) x
pivot 
(
max(FieldValue)
for FieldName in (' + @cols + ')
) p '
execute(@query)

示例：db<>小提琴

Answer 2

您可以使用条件聚合作为

select rn,
max(case when colname = 'Field1' then colvalue end) col1,
max(case when colname = 'Field2' then colvalue end) col2,
max(case when colname = 'Field3' then colvalue end) col3 
from
(
values
('Field1', 'F1value1', 0),
('Field2', 'F2value1', 0),
('Field3', 'F3value1', 0),
('Field1', 'F1value2', 1),
('Field2', 'F2value2', 1),
('Field3', 'F3value2', 1)
) t(colname, colvalue, rn)
group by rn

退货：

+----+----------+----------+----------+
| rn |   col1   |   col2   |   col3   |
+----+----------+----------+----------+
|  1 | F1value1 | F2value1 | F3value1 |
|  2 | F1value2 | F2value2 | F3value2 |
+----+----------+----------+----------+