Dynamic pivot unknown number of rows – Sql View

I’m using SQL to create a database VIEW from the table below.

ItemCode                |BcdName           |BcdCode      |
------------------------|------------------|-------------|
03616006.ERA            |EAS               |00060914ER   |
03616006.ERA            |EAN               |8053379025831|
02660402.ERA            |EAS               |00060936ER   |
02660402.ERA            |EAN               |8053379026227|
02660402.ERA            |EANBULK           |00060936ET   |

I need to pivot the BcdName (for an unknown number of columns) and get the following result:

ItemCode                |EAN               |EAS          |EANBULK      |
------------------------|------------------|-------------|-------------|
03616006.ERA            |8053379025831     |00060914ER   |             |
02660402.ERA            |8053379026227     |00060936ER   |00060936ET   |

The solution found here: dynamic pivot issue in sql server 2012 is not working on a table View.

Answer

If you are willing to settle for defined columns with a “Warning/Undefined” column

Example

Select * 
 From  (Select ItemCode
              ,Item     = case when [BcdName] in ('EAN','EAS','EANBULK','EASBULK') then BcdName else 'Empty' end
              ,Value    = [BcdCode]
          From YourTable
       ) src
 Pivot (max(Value) for Item in ([EAN],[EAS],[EANBULK],[EASBULK],[EMPTY]) ) Pvt

Returns

ItemCode        EAN             EAS         EANBULK     EASBULK EMPTY
02660402.ERA    8053379026227   00060936ER  00060936ET  NULL    SomeValue
03616006.ERA    8053379025831   00060914ER  NULL        NULL    NULL