pivot a table with cell name as column name I have a table structure is :

year | code | name| value | wef        | 
2014 | a001 | abe | 2000  | 2014-04-01 | 
2014 | a001 | def | 3000  | 2014-05-01 |
2014 | a002 | abe | 2000  | 2014-06-01 |
2014 | a003 | def | 2000  | 2014-04-01 |
2014 | a003 | mno | 5000  | 2014-06-01 |


i need result in format as: 

year | code | abe | abe__wef | def | def__wef  | mno | mno____wef |   
2014 | a001 | 2000|2014-04-01|3000 | 2014-05-01|     |  
2014 | a002 | 2000|2014-06-01|     |           |     | 
2014 | a003 |     |          |2000 | 2014-04-01| 5000| 2014-06-01
You can use the PIVOT function to get the result but since you have multiple values that you want to convert into columns, you will first want to look at unpivoting the value and wef columns first.

You didn't specify which version of SQL Server you are using but you can use either the UNPIVOT function or you can use CROSS APPLY with VALUES or UNION ALL to convert value/wef into multiple rows. 

The syntax will be similar to:

select year, code, col, val
from dbo.yourtable
cross apply
(
  select name, convert(varchar(10), value) union all
  select name+'_wef', convert(varchar(10), wef, 120)
) c (col, val);


See SQL Fiddle with Demo.  This converts your data into the format:

| YEAR | CODE |     COL |        VAL |
|------|------|---------|------------|
| 2014 | a001 |     abe |       2000 |
| 2014 | a001 | abe_wef | 2014-04-01 |
| 2014 | a001 |     def |       3000 |
| 2014 | a001 | def_wef | 2014-05-01 |
| 2014 | a002 |     abe |       2000 |
| 2014 | a002 | abe_wef | 2014-06-01 |


Now you can apply the PIVOT function:

select year, code,
  abe, abe_wef, def, def_wef, mno, mno_wef
from
(
  select year, code, col, val
  from dbo.yourtable
  cross apply
  (
    select name, convert(varchar(10), value) union all
    select name+'_wef', convert(varchar(10), wef, 120)
  ) c (col, val)
) d
pivot
(
  max(val)
  for col in (abe, abe_wef, def, def_wef, mno, mno_wef)
) piv;


See SQL Fiddle with Demo.

The problem that you might run into with the version above is it works great when you have a limited number of values because you can hard-code the query. But if you have an unknown number of values, then the solution won't work. In situations where you have an unknown number of values then you will need to use dynamic sql to get the final result:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(col) 
                    from dbo.yourtable
                    cross apply
                    (
                      select name, 1 union all
                      select name+'_wef', 2 
                    ) c (col, so)
                    group by col, so
                    order by col, so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT year, code,' + @cols +N' 
            from 
            (
              select year, code, col, val
              from dbo.yourtable
              cross apply
              (
                select name, convert(varchar(10), value) union all
                select name+''_wef'', convert(varchar(10), wef, 120)
              ) c (col, val)
            ) x
            pivot 
            (
                max(val)
                for col in (' + @cols + N')
            ) p '

exec sp_executesql @query;


See SQL Fiddle with Demo. Both versions will give a result of:

| YEAR | CODE |    ABE |    ABE_WEF |    DEF |    DEF_WEF |    MNO |    MNO_WEF |
|------|------|--------|------------|--------|------------|--------|------------|
| 2014 | a001 |   2000 | 2014-04-01 |   3000 | 2014-05-01 | (null) |     (null) |
| 2014 | a002 |   2000 | 2014-06-01 | (null) |     (null) | (null) |     (null) |
| 2014 | a003 | (null) |     (null) |   2000 | 2014-04-01 |   5000 | 2014-06-01 |