troubleshooting Question

SQL query to display as PIVOT

Avatar of Abdul Khan
Abdul Khan asked on
DatabasesMicrosoft SQL ServerSQL
6 Comments1 Solution84 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros