We help IT Professionals succeed at work.
Get Started

SQL query to display as PIVOT

80 Views
Last Modified: 2017-11-15
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
Comment
Watch Question
Database Expert
Awarded 2016
Top Expert 2016
Commented:
This problem has been solved!
Unlock 1 Answer and 6 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE