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.
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