Avatar of Abdul Khan
Abdul Khan
 asked on

SQL query to display as PIVOT

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
DatabasesMicrosoft SQL ServerSQL

Avatar of undefined
Last Comment
Pawan Kumar

8/22/2022 - Mon
NerdsOfTech

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

ASKER
MSsql database

thanks.
Pawan Kumar

Out of the three lines which one you want.?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
Pawan Kumar

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Abdul Khan

ASKER
Thanks so much Pawan, It's exactly what I am looking for!
Pawan Kumar

welcome. glad to help.