sql server - SQL pivot - trying to convert first row to columns -

i have data imported csv file looks this:

name     field     year01     year02 ... year10 ----------------------------------------------- name     fieldname 2006       2007   ... 2015 xyz      field_a   123        999        222 xyz      field_b   111.1      123.4      456.7 abc      field_a   444        555        890 abc      field_b   999.9      888.8      789.0 

which need this:

name  year   field_a   field_b ----------------------------------------------- xyz   2006   123       111.1 xyz   2007   999       123.4 xyz   2015   222       456.7 ... abc   2006   444       999.9 abc   2007   555       888.8 abc   2015   890       789.0 

i'm sure there's way using pivot instruction can't seem make work. how can achieve this?

pivot unpivot table. steps:

1. rename table columns
2. unpivot year
3. pivot field

complete query:

    t1     (         select *         (values              ('name', 'fieldname', 2006, 2007, 2015),              ('xyz', 'field_a', 123  ,999  ,222  ),             ('xyz', 'field_b', 111.1,123.4,456.7),             ('abc', 'field_a', 444  ,555  ,890  ),             ('abc', 'field_b', 999.9,888.8,789.0)         ) t(name,     field,     year01,     year02, year10)     )     select *          (         select name name, field fieldname, year01 [2006], year02 [2007], year10 [2015]         t1         name <> 'name'     ) t2     unpivot     (         [value] [year] in ([2006], [2007], [2015])     )     pivot     (         min(value) fieldname in (field_a, field_b)     ) p     order name desc, [year]