sql - 需要数据透视表/交叉表查询帮助

Question

我有一个像下面这样的查询，它给出了以下结果。

查询

select OrderID, AccessName, Address1, Postcode, GeneralInstructions, --CompanyName,
col, --col+'_'+cast(rn as varchar(10)) col, 
val
from 
(
   select o.OrderID, js.JobStatusID, p.Name, o.AccessName, o.Address1, o.Postcode,   oj.GeneralInstructions, s.CompanyName
  , row_number() over(partition by o.OrderID 
                      order by js.JobStatusID) rn
FROM 
NEPCCO.Orders o
inner join NEPCCO.Clients c on o.ClientID = c.ClientID
inner join NEPCCO.OrderJobs oj on o.OrderID = oj.OrderID
inner join NEPCCO.Suppliers s on oj.SupplierID = s.SupplierID
inner join NEPCCO.Products p on oj.ProductID = p.ProductID
inner join NEPCCO.OrderStatus os on o.OrderStatusID = os.OrderStatusID
inner join NEPCCO.JobStatus js on oj.JobStatusID = js.JobStatusID
where 
o.OrderID in (50211, 44621) and 
(p.ProductID in (35,36,37,38,38,40,41,42,43) or  p.ProductID in (33,34))
 ) d
 unpivot
 (
   val
   for col in (Name)
) un

第一个输出

OrderID     AccessName      Address1                PostCode   Col Val    
44621   Mr Hayden   7 Broad Rush Green  LU7 2XA    Name  FPA - 28
44621   Mr Hayden   7 Broad Rush Green  LU7 2XA    Name  AW EPR
50211   Mrs Godwin  64 Riverview    MK43 7PN       Name  FPA - 28

另一个查询相同的东西以获得所需的输出

select *
from
(
   select OrderID, AccessName, Address1, Postcode, GeneralInstructions, --CompanyName,
    col, --col+'_'+cast(rn as varchar(10)) col, 
    val
  from 
  (
    select o.OrderID, js.JobStatusID, p.Name, o.AccessName, o.Address1, o.Postcode, oj.GeneralInstructions, s.CompanyName
     , row_number() over(partition by o.OrderID,js.JobStatusID 
                      order by o.OrderID) rn
   FROM 
   NEPCCO.Orders o
   inner join NEPCCO.Clients c on o.ClientID = c.ClientID
   inner join NEPCCO.OrderJobs oj on o.OrderID = oj.OrderID
   inner join NEPCCO.Suppliers s on oj.SupplierID = s.SupplierID
   inner join NEPCCO.Products p on oj.ProductID = p.ProductID
   inner join NEPCCO.OrderStatus os on o.OrderStatusID = os.OrderStatusID
   inner join NEPCCO.JobStatus js on oj.JobStatusID = js.JobStatusID
   where 
   o.OrderID in (50211, 44621) and 
   (p.ProductID in (35,36,37,38,38,40,41,42,43) or  p.ProductID in (33,34))
    ) d
   unpivot
  (
   val
   for col in (Name)
  ) un
  ) s
 pivot
 (
 max(val)
 for col in (Name,JobStatusID,  JobStatusID1, Name1)
) piv

第二个输出接近想要的但不是我想要的

OrderID     AccessName      Address1            PostCode   Col              Val 
 44621  Mr Hayden   7 Broad Rush Green  LU7 2XA  FPA - 28   NULL     
 50211  Mrs Godwin  64 Riverview        MK43 7PN     FPA - 28   NULL    

所需的输出 - 从第一个查询中检查我想显示的列标题名称

OrderID     AccessName      Address1           PostCode   InstallJobType  EPR Type   
 44621  Mr Hayden   7 Broad Rush Green LU7 2XA    FPA - 28        AW EPR
 50211  Mrs Godwin  64 Riverview    MK43 7PN      FPA - 28        NULL  

任何帮助将不胜感激，因为我被卡住了

UNPIVOT 之前的数据

OrderID     JobstatusId   Name      AccessName        Address1             PostCode    CompanyName         M

   44621    3         AW EPR    Mr Hayden   7 Broad Rush Green  LU7 2XA   N/A (Sole trader)                 1
   44621    14        FPA - 28  Mr Hayden   7 Broad Rush Green  LU7 2XA Just Energy Solutions Limited       2
   50211    17        FPA - 28  Mrs Godwin  64 Riverview        MK43 7PN    Just Energy Solutions Limited   1

编辑 - 请检查我是否做得对，因为它提供了我想要的输出

select OrderID, AccessName, Address1, Postcode, GeneralInstructions, 
Name_1 as "Install Jobs", 
Name_2 as "EPR Jobs",
CompanyName,Name
from
(
  select OrderID, AccessName, Address1, Postcode, GeneralInstructions, CompanyName,
    col+'_'+cast(rn as varchar(10)) col, 
    val
  from 
  (
    select o.OrderID, js.JobStatusID, p.Name, o.AccessName, o.Address1, o.Postcode, oj.GeneralInstructions, s.CompanyName, 
    js.Name as "Install Job Status", js.JobStatusID as JS1
      , row_number() over(partition by o.OrderID 
                          order by o.OrderID) rn
    FROM 
    NEPCCO.Orders o
    inner join NEPCCO.Clients c on o.ClientID = c.ClientID
    inner join NEPCCO.OrderJobs oj on o.OrderID = oj.OrderID
    inner join NEPCCO.Suppliers s on oj.SupplierID = s.SupplierID
    inner join NEPCCO.Products p on oj.ProductID = p.ProductID
    inner join NEPCCO.OrderStatus os on o.OrderStatusID = os.OrderStatusID
    inner join NEPCCO.JobStatus js on oj.JobStatusID = js.JobStatusID
    where 
    o.OrderID in (50211, 44621) and 
    (p.ProductID in (35,36,37,38,38,40,41,42,43) or  p.ProductID in (33,34))
  ) d
  unpivot
  (
    val
    for col in (Name)
  ) un
) s
pivot
(
  max(val)
  for col in (JobStatusID_1, Name_1,  JobStatusID_2, Name_2,Name)
) piv

Answer 1

您当前的查询令人困惑，因为您仅将 UNPIVOT 应用于 Name列。 UNPIVOT 用于将多列转换为多行。根据您在查询的 PIVOT 部分中的列，我猜您需要取消透视 name和 JobStatus列。

如果这是正确的，那么您的查询应该类似于以下内容:

select *
from
(
   select OrderID, AccessName, Address1, Postcode, 
    GeneralInstructions, --CompanyName,
    col+cast(rn as varchar(10)) col,
    val
  from 
  (
    select o.OrderID, 
      cast(js.JobStatusID as varchar(50)) JobStatusId, 
      cast(p.Name as varchar(50)) Name, 
      o.AccessName, o.Address1, o.Postcode, 
      oj.GeneralInstructions, s.CompanyName
     , row_number() over(partition by o.OrderID,js.JobStatusID 
                      order by o.OrderID) rn
     FROM NEPCCO.Orders o
     inner join NEPCCO.Clients c on o.ClientID = c.ClientID
     inner join NEPCCO.OrderJobs oj on o.OrderID = oj.OrderID
     inner join NEPCCO.Suppliers s on oj.SupplierID = s.SupplierID
     inner join NEPCCO.Products p on oj.ProductID = p.ProductID
     inner join NEPCCO.OrderStatus os on o.OrderStatusID = os.OrderStatusID
     inner join NEPCCO.JobStatus js on oj.JobStatusID = js.JobStatusID
     where o.OrderID in (50211, 44621) and 
       (p.ProductID in (35,36,37,38,38,40,41,42,43) or  p.ProductID in (33,34))
  ) d
  unpivot
  (
    val
    for col in (Name, JobStatus)
  ) un
) s
pivot
(
  max(val)
  for col in (Name, JobStatusID,  JobStatusID1, Name1)
) piv;

编辑，如果您要拥有未知数量的值，那么您将需要使用动态 SQL:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT  ',' + QUOTENAME(col+cast(seq as varchar(10))) 
                    from
                    (
                       select   row_number() over(partition by o.OrderID,js.JobStatusID 
                          order by o.OrderID) seq
                       FROM NEPCCO.Orders o
                       inner join NEPCCO.Clients c on o.ClientID = c.ClientID
                       inner join NEPCCO.OrderJobs oj on o.OrderID = oj.OrderID
                       inner join NEPCCO.Suppliers s on oj.SupplierID = s.SupplierID
                       inner join NEPCCO.Products p on oj.ProductID = p.ProductID
                       inner join NEPCCO.OrderStatus os on o.OrderStatusID = os.OrderStatusID
                       inner join NEPCCO.JobStatus js on oj.JobStatusID = js.JobStatusID
                    ) d
                    cross apply
                    (
                      select 'Name', 1 union all
                      select 'JobStatusId', 2
                    ) c (col, so)
                    group by seq, col, so
                    order by seq, so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT *
            from 
            (
               select OrderID, AccessName, Address1, Postcode, 
                GeneralInstructions, --CompanyName,
                col+cast(rn as varchar(10)) col,
                val
              from 
              (
                select o.OrderID, 
                  cast(js.JobStatusID as varchar(50)) JobStatusId, 
                  cast(p.Name as varchar(50)) Name, 
                  o.AccessName, o.Address1, o.Postcode, 
                  oj.GeneralInstructions, s.CompanyName
                 , row_number() over(partition by o.OrderID,js.JobStatusID 
                                  order by o.OrderID) rn
                 FROM NEPCCO.Orders o
                 inner join NEPCCO.Clients c on o.ClientID = c.ClientID
                 inner join NEPCCO.OrderJobs oj on o.OrderID = oj.OrderID
                 inner join NEPCCO.Suppliers s on oj.SupplierID = s.SupplierID
                 inner join NEPCCO.Products p on oj.ProductID = p.ProductID
                 inner join NEPCCO.OrderStatus os on o.OrderStatusID = os.OrderStatusID
                 inner join NEPCCO.JobStatus js on oj.JobStatusID = js.JobStatusID
                 where o.OrderID in (50211, 44621) and 
                   (p.ProductID in (35,36,37,38,38,40,41,42,43) or  p.ProductID in (33,34))
              ) d
              unpivot
              (
                val
                for col in (Name, JobStatus)
              ) un
            ) x
            pivot 
            (
                max(val)
                for col in (' + @cols + ')
            ) p '

execute(@query)