display unique field out of 10 records in sql

Question

My sql query is

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_]

And my result is

In this out of 21 rows i am having 17 rows as kit. I need to group this kit and to display in Old Product Code as Kit in one row instead of 17 rows.

Answer 1

Looking at what you are trying to do you might be able to use something like this to return the data:

;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) 

See SQL Fiddle with Demo

Edit, if you want to order this in a specific way you can use Order By with a CASE statement:

;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]

See SQL Fiddle with Demo