Link to home
Start Free TrialLog in
Avatar of Abdul Khan
Abdul Khan

asked on

SQL query to display as PIVOT

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
Avatar of NerdsOfTech
Flag of United States of America image

Please specify what database and version you are using.
Avatar of Abdul Khan
Abdul Khan


MSsql database

Out of the three lines which one you want.?
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks so much Pawan, It's exactly what I am looking for!
welcome. glad to help.