Select
faa.AssetID, faa.AssetNumber, faa.Status, faa.Dept, faa.Class, faa.Description, faa.PropertyNumber,faa.AcquiredDateTime,
case When fayr.ValueID = 'COST' THEN fayr.Amount else 0 end 'Cost',
case when fayr.ValueID = 'ACC DEPR' THEN fayr.Amount else 0 end 'Accum Depr',
case when fayr.ValueID = 'BOOK' THEN fayr.Amount else 0 end 'Book value'
FROM [Livendb].[dbo].[FaAssets] faa with (nolock)
join [Livendb].[dbo].[FaAssetAmountsByYear] fayr with (nolock)
on faa.AssetID = fayr.AssetID and faa.SourceID = fayr.SourceID
where faa.[Status] = 'ACTIVE' and faa.AssetID = '6' and fayr.FiscalYearID = '2018' and fayr.ValueID in ('BOOK','ACC DEPR','COST')
I am getting multiple line, would like to combine 3 lines in to one so the Last 3 columns (cost, Accum Depr and Book Value) are all shown into on line, here is what I have right now:
AssetID AssetNumber Status Dept Class Description PropertyNumber AcquiredDateTime Cost Accum Depr Book value
6 0000000005 ACTIVE 15.9950 15BLDG GENERAL HOSPITAL NULL 2013-12-01 00:00:00.000 0.00 0.00 83722355.32
6 0000000005 ACTIVE 15.9950 15BLDG GENERAL HOSPITAL NULL 2013-12-01 00:00:00.000 0.00 9087644.68 0.00
6 0000000005 ACTIVE 15.9950 15BLDG GENERAL HOSPITAL NULL 2013-12-01 00:00:00.000 92810000.00 0.00 0.00