如何在 SQL 中使用 PIVOT 获得实际结果

Question

我正在使用查询并得到结果。但我不需要这个结果，我需要另一个结果。我知道查询对于我想显示的结果是错误的。我很困惑地询问结果是如何出来的，以及是否有其他方法可以得出这个结果。

declare @temp table(
  ProductId int,
  Caption nvarchar(max),
  Value nvarchar(max)
)
insert into @temp values (6830,'Stone Cut','Full Cut')
insert into @temp values (6830,'Stone Cut','Single Cut')
insert into @temp values (6830,'Gem Type','Diamond')
insert into @temp values (6830,'Gem Type','Diamond')
insert into @temp values (6830,'Total Diamond Weight','0.34')
insert into @temp values (6831,'Stone Cut','Full Cut')
insert into @temp values (6831,'Stone Cut','Single Cut')
insert into @temp values (6831,'Gem Type','Diamond')
insert into @temp values (6831,'Gem Type','Diamond')
insert into @temp values (6831,'Total Diamond Weight','0.35')
select ProductId
  , (case when [StoneCut] is null then '' else [StoneCut] end) as [StoneCut]
  , (case when [GemType] is null then '' else [GemType] end) as [GemType]
  , (case when [TotalDiamondWeight] is null then '' else [TotalDiamondWeight] end) as [TotalDiamondWeight]
from
(
  select ProductId, Caption, Value
  from @temp
) x
pivot
(
  max(Value)
  for Caption in([StoneCut], [GemType], [TotalDiamondWeight])
)p;

它显示一行像

---------------------------------------------------
ProductId | StoneCut | GemType | TotalDiamondWeight
---------------------------------------------------
 6830     |Single Cut| Diamond | 0.34

但我想要这个输出：

---------------------------------------------------
ProductId | StoneCut | GemType | TotalDiamondWeight
---------------------------------------------------
 6830     |Full Cut  | Diamond | 0.34
 6830     |Single Cut| Diamond |
 6831     |Full Cut  | Diamond | 0.34
 6831     |Single Cut| Diamond |

Answer 1

您

有一行ProductId因为每一行总是相同的。当您使用运算符 MAX() 时，您有一行具有 VALUE 的MAX()。 但是，如果您想拥有其他行，则只需区分这些行：

SELECT 
  p.ProductId
, p.TotalDiamondWeight
, p.GemType
, p.StoneCut
FROM
(
    SELECT ProductId,
           Caption,
           Value,
           ROW_NUMBER() OVER (PARTITION BY Caption ORDER BY Value) RN
    FROM @temp
) x
PIVOT
(
    MAX(Value)
    FOR Caption IN ([StoneCut], [GemType], [TotalDiamondWeight])
) p;

Answer 2

您可以通过使用 Row_number() 向集合添加唯一行来尝试此操作。

declare @temp table(
  ProductId int,
  Caption nvarchar(max),
  Value nvarchar(max)
)
insert into @temp values (6830,'Stone Cut','Full Cut')
insert into @temp values (6830,'Stone Cut','Single Cut')
insert into @temp values (6830,'Gem Type','Diamond')
insert into @temp values (6830,'Gem Type','Diamond')
insert into @temp values (6830,'Total Diamond Weight','0.34')
insert into @temp values (6831,'Stone Cut','Full Cut')
insert into @temp values (6831,'Stone Cut','Single Cut')
insert into @temp values (6831,'Gem Type','Diamond')
insert into @temp values (6831,'Gem Type','Diamond')
insert into @temp values (6831,'Total Diamond Weight','0.35')
   SELECT productid, 
       [Stone Cut], 
      [Gem Type], 
      [Total Diamond Weight]
    FROM (SELECT * 
        FROM   (SELECT t.*, 
                       Row_number() 
                         OVER ( 
                           partition BY productid,T.caption 
                           ORDER BY (SELECT 1)) RN 
               FROM   @temp t)x 
               PIVOT (Max(value) 
                     FOR caption IN([Stone Cut], 
                                    [Gem Type], 
                                    [Total Diamond Weight]))p
             ) t1 
    ORDER BY productid

您将获得所需的输出。

输出：

+-----------+------------+----------+----------------------+
| productid | Stone Cut  | Gem Type | Total Diamond Weight |
+-----------+------------+----------+----------------------+
| 6830      | Full Cut   | Diamond  | 0.34                 |
+-----------+------------+----------+----------------------+
| 6830      | Single Cut | Diamond  | NULL                 |
+-----------+------------+----------+----------------------+
| 6831      | Full Cut   | Diamond  | 0.35                 |
+-----------+------------+----------+----------------------+
| 6831      | Single Cut | Diamond  | NULL                 |
+-----------+------------+----------+----------------------+

Answer 3

这

能回答你的问题吗？

select * from (
select 
 ProductId, 
 StoneCut, 
 max(GemType) over (partition by ProductId) GemType,
 max(TotalDiamondWeight) over (partition by ProductId) TotalDiamondWeight
from
(
  select 
    ProductId, 
    IIF(Caption = 'StoneCut', Value, '') StoneCut,
    IIF(Caption = 'GemType', Value, '') GemType,
    IIF(Caption = 'TotalDiamondWeight', Value, '') TotalDiamondWeight
  from @temp
) t
)t2
where StoneCut != ''

小提琴

Answer 4

似乎您必须获得表格的 2 组并将它们与 UNION 组合：

select
  ProductId,
  min(case when Caption = 'StoneCut' then Value end) StoneCut,
  min(case when Caption = 'GemType' then Value end) GemType,
  min(case when Caption = 'TotalDiamondWeight' then Value end) TotalDiamondWeight
from temp
group by ProductId
union 
select
  ProductId,
  max(case when Caption = 'StoneCut' then Value end) StoneCut,
  max(case when Caption = 'GemType' then Value end) GemType,
  max(case when Caption = 'TotalDiamondWeight' then Value end) TotalDiamondWeight
from temp
group by ProductId

观看演示