Change column in Rows

• syed_3177

  SSC Enthusiast

  Points: 167

  More actions

  September 20, 2018 at 1:59 am

  #372888

  Hi , I have a table below where Item is repeated for type Parts and service.

  Item		Type		Cost_Of_Sales		Quantity
  11827350		Parts		11.5		2
  11827350		Service		5.880235		2
  55933075		Parts		7.25		1
  55933075		Service		22.8		3
  60326154		Parts		6.75		4
  60326161		Parts		0.292		1
  60327210		Parts		6.25		1
  60427220		Parts		5.75		1
  60427220		Service		3.70343		1
  64726175		Parts		18		2
  64726175		Service		24		2

  But i need to display itemNo once and cost of sales and quantity for Parts and Service in one line as below.

  No_		P_Cost_Of_Sales		P_Quantity		S_Cost_OF_Sales		S_Quantity
  11827350		11.5		2		5.880235		2
  55933075		7.25		1		22.8		3
  60326154		6.75		4		0		0
  60326161		0.292		1		0		0
  60327210		6.25		1		0		0
  60427220		5.75		1		3.70343		1
  64726175		18		2		24		2

  Thanks

• laurie-789651

  SSCertifiable

  Points: 7680

  More actions

  September 20, 2018 at 3:29 am

  #2006393

  USE [tempdb]

  DROP TABLE [dbo].[Test1];

  CREATE TABLE [dbo].[Test1]
(Item Varchar(10), [Type] Varchar(20), Cost_Of_Sales Decimal(12, 6), Quantity Int);

  INSERT INTO [dbo].[Test1]
VALUES
('11827350',        'Parts',    11.5,        2),
('11827350',        'Service',    5.880235,    2),
('55933075',        'Parts'    ,    7.25,        1),
('55933075',        'Service',    22.8,        3),
('60326154',        'Parts',    6.75,        4),
('60326161',        'Parts'    ,    0.292,        1),
('60327210',        'Parts',    6.25,        1),
('60427220',        'Parts'    ,    5.75,        1),
('60427220',        'Service',    3.70343,    1),
('64726175',        'Parts',    18,            2),
('64726175',        'Service',    24,            2);

  --SELECT * FROM [dbo].[Test1];

  SELECT [Item]
    ,P_Cost_Of_Sales = MAX(CASE WHEN [Type] = 'Parts' THEN Cost_Of_Sales ELSE 0 END)
    ,P_Quantity = MAX(CASE WHEN [Type] = 'Parts' THEN Quantity ELSE 0 END)
    ,S_Cost_OF_Sales = MAX(CASE WHEN [Type] = 'Service' THEN Cost_Of_Sales ELSE 0 END)
    ,S_Quantity = MAX(CASE WHEN [Type] = 'Service' THEN Quantity ELSE 0 END)
FROM [dbo].[Test1]
GROUP BY [Item];