SQL - PIVOT for one column and add new column

Question

I am fairly new to SQL. I have got this input table as

TypeId   EventDescription   FeedHeader     FeedHeaderValue
---------------------------------------------------------
 166         Financial       AllocRule         130
 166         Financial        DealID            0
 175       Partner Capital   InvestorID       OV_P1
 175         Investment        Querter          Q1
 175         Investment         DealID        offset
 175         Investment       InvestorID       OV_P2

I need an output as follows

 Financial    value     Partner Capital     value      Investment       value
-------------------------------------------------------------------------------
 AllocRule    130           InvestorID       OV_P1        Querter          Q1
 DealID        0                                          DealID           offset
                                                        InvestorID        OV_P2

Not sure if that is even possible. I tried using pivot but its not giving desired output

 select 
 [Financial] as FinancialHeader
 , [Partner Capital] as PartnerCapitalHeader
 , [Investment] as Investmentheader
  from
 (
 select  EventDescription, FeedHeader
 from [Feeder]
 ) x
 pivot
 (
   MAX(FeedHeader)
   for EventDescription in([Financial], [Partner Capital], [Investment])
  )p

Another approach i tried

Select 
    Min(Case [EventDescription] When 'Financial' Then [FeedHeader] End) 
Financial,
    Min(Case [EventDescription] When 'Financial' Then [FeedHeaderValue] End) 
 value,
     Min(Case [EventDescription] When 'Partner Capital' Then [FeedHeader] 
 End) PartnerCapital,
     Min(Case [EventDescription] When 'Partner Capital' Then 
[FeedHeaderValue] End) value,
   Min(Case [EventDescription] When 'Investment' Then [FeedHeader] End) 
 Investment,
   Min(Case [EventDescription] When 'Investment' Then [FeedHeaderValue] End) 
 value
   From [Feeder]
    Group By EventDescription

Is there a another way to do it?

Answer 1

I was curious and did some research with PIVOT on SO and google and finally my luck clicked (at least what I think now)

The key point here is that you create new EventDescription values by appending 1 or 2 to the end depending on how many columns we want to PIVOT.

Without doing this, the pivot query won't work properly and would lead to error as per my experience with this task.

select max([Financial]) as FinancialHeader
     , max([Financial1]) as FinancialHeaderValue
     , max([Partner Capital]) as PartnerCapitalHeader
     , max([Partner Capital1]) as PartnerCapitalHeaderValue
     , max([Investment]) as InvestmentHeader
     , max([Investment1]) as InvestmentHeaderValue
from
 (select EventDescription,
         EventDescription+'1' as EventDescription1, 
         FeedHeader,
         FeedHeaderValue,
         row_number() over (partition by EventDescription order by EventDescription) rn
    from [testtable]
 ) x
 pivot
 (
   MAX(FeedHeader)
   for EventDescription in([Financial], [Partner Capital], [Investment])
 ) p
  pivot
 (
   MAX(FeedHeaderValue)
   for EventDescription1 in([Financial1], [Partner Capital1] , [Investment1] )
 ) v
group by [RN]

DEMO: db<>fiddle