SQL Query

Transact-SQL
1

Hello,

I have a table below

CREATE TABLE [dbo].[Trn_Invoice](
	[Inv_No] [numeric](18, 0) NULL,
	[Inv_PhNo] [nvarchar](50) NULL,
	[Inv_OpRate] [numeric](18, 0) NULL,
	[Inv_STBRate] [numeric](18, 0) NULL,
	[Inv_Type] [nvarchar](50) NULL,
	[Inv_Days] [numeric](18, 0) NULL
) ON [PRIMARY]

GO

below is the data.


INSERT INTO [Trn_Invoice]
  ( [Inv_No]
      ,[Inv_PhNo]
      ,[Inv_OpRate]
      ,[Inv_STBRate]
      ,[Inv_Type]
      ,[Inv_Days] )
VALUES
  (1,	'A1',	100,	50,	'OP',	2),
(1,	'A1',	100,	50,	'STB',	3),
(1,	'A2',	200,	150,	'OP',	4),
(1,	'A2',	200,	150,	'STB',	5)

im searching for the below result.

Inv_No Inv_PhNo OP Rate STB Rate OP Days STB Days Total
1 A1 100 50 2 3 350
1 A2 200 150 4 5 1550

The Total is OP RateOP Days + STB RateSTB Days,

I tried the Pivot Query but i got the output only below.

Inv_No Inv_PhNo OP Rate STB Rate
1 A1 100 50
1 A2 200 150

Thanks & Regards,
Basit.

2 
WITH Grps
AS
(
	SELECT Inv_No, Inv_PhNo
		,MAX(Inv_OPRate) AS OPRate
		,MAX(Inv_STBRate) AS STBRate
		,MAX(CASE WHEN Inv_Type = 'OP' THEN Inv_Days ELSE 0 END) AS OPDays
		,MAX(CASE WHEN Inv_Type = 'STB' THEN Inv_Days ELSE 0 END) AS STBDays
	FROM dbo.Trn_Invoice
	GROUP BY Inv_No, Inv_PhNo
)
SELECT Inv_No, Inv_PhNo, OPRate, STBRate, OPDays, STBDays
	,OPRate * OPDays + STBRate * STBDays AS Total
FROM Grps;