sql server pivot syntax

Question

I need to do row column transpose and tried a following query

select txn_date,
case when remarks is NULL then 'bank' else remarks  end as remarks
 from nibl
  PIVOT
(
count(txn_date)
FOR remarks IN (bank, remit)
) as pivot

the query above is giving syntax error as below

Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'pivot'.

Answer 1

pivot is a reserved word in SQL Server so it is ) as pivot that fails here.

Use another alias ) as p.

Answer 2

did u try it in this way...

select * from
(select txn_date,
case when remarks is NULL then 'bank' else remarks  end as remarks
 from nibl
 ) srs
  PIVOT
(
count(txn_date)
FOR remarks IN (bank, remit)
) as pivot