唯一显示出现场的10条记录中的SQL(display unique field out of 10

我的SQL查询

select I.[Old Product Code], 
    I.[Trade Name], 
    I.[Short Name], 
    SIL.[BOM Item No_] ,
    CASE when SIL.[Dimension Group Code] = 'IOL' 
        then I.[Group Description] 
        else I.[Short Name] END as GD,
    CASE when SIL.[BOM Item No_] <> '' 
        then 'Kit' end
from [Sales Invoice Header] SIH, [Sales Invoice Line]  SIL, [Item] I 
where I.No_ =  SIL.No_ 
    and SIL.[Document No_] = 'PEXP1213-153' 
    and SIH.No_ = SIL.[Document No_] 
group by I.[Old Product Code], I.[Trade Name], I.[Short Name],  
    SIL.[Dimension Group Code], I.[Group Description], SIL.[BOM Item No_]

而我的结果是

在该出的21行我有17行作为试剂盒。我需要这组套件和老产品编号为包在一个行，而不是17行显示。

看着你正在尝试做的，你也许能够使用这样的返回数据：

;with data as
(
  select I.[Old Product Code], 
      I.[Trade Name], 
      I.[Short Name], 
      SIL.[BOM Item No_] ,
      CASE when SIL.[Dimension Group Code] = 'IOL' 
          then I.[Group Description] 
          else I.[Short Name] END as GD,
      CASE when SIL.[BOM Item No_] <> '' 
          then 'Kit' end CombinedKit
  from SalesInvoiceHeader SIH
  inner join SalesInvoiceLine  SIL
    on SIH.No_ = SIL.[Document No_] 
  inner join Item I 
    on I.No_ =  SIL.No_ 
  where SIL.[Document No_] = 'PEXP1213-153' 
),
d2 as
(
  select [Old Product Code],
    [Trade Name],
    [Short Name],
    [BOM Item No_],
    GD,
    CombinedKit,
    row_number() 
      over(partition by CombinedKit order by [Old Product Code]) rn
  from data
) 
select 
    case when combinedkit = 'kit' 
        then 'Kit' else [Old Product Code] end  [Old Product Code],
    [Trade Name],
    [Short Name],
    [BOM Item No_],
    GD
    --, CombinedKit
from d2
where (CombinedKit = 'Kit' and rn = 1)
  or (CombinedKit is null)

请参阅SQL拨弄演示

编辑，如果你想在你可以用一种特定的方式来订购此Order By一个CASE语句：

;with data as
(
  select I.[Old Product Code], 
      I.[Trade Name], 
      I.[Short Name], 
      SIL.[BOM Item No_] ,
      CASE when SIL.[Dimension Group Code] = 'IOL' 
          then I.[Group Description] 
          else I.[Short Name] END as GD,
      CASE when SIL.[BOM Item No_] <> '' 
          then 'Kit' end CombinedKit
  from SalesInvoiceHeader SIH
  inner join SalesInvoiceLine  SIL
    on SIH.No_ = SIL.[Document No_] 
  inner join Item I 
    on I.No_ =  SIL.No_ 
  where SIL.[Document No_] = 'PEXP1213-153' 
),
d2 as
(
  select [Old Product Code],
    [Trade Name],
    [Short Name],
    [BOM Item No_],
    GD,
    CombinedKit,
    row_number() 
      over(partition by CombinedKit order by [Old Product Code]) rn
  from data
) 
select 
    case when combinedkit = 'kit' 
        then 'Kit' else [Old Product Code] end  [Old Product Code],
    [Trade Name],
    [Short Name],
    [BOM Item No_],
    GD
    --, CombinedKit
from d2
where (CombinedKit = 'Kit' and rn = 1)
  or (CombinedKit is null) 
order by 
  case when combinedkit = 'kit' then 0 else 1 end, [Old Product Code]

请参阅SQL拨弄演示