Covert a duplicate Row value into column using pivot table

Here is my stored procedure query to fetch data in first table.

SELECT [Item] = t3.Item,
[Name] = t2.Name,
[Value] = t1.value

INTO #Result

FROM table1 t1
INNER JOIN table2 t2 ON t2.IsDeleted = 0
INNER JOIN table3 t3 ON t3.IsDeleted = 0 AND t3.Item_ID = @Id
WHERE t1.Item_ID = @Id 

GROUP BY 
t1.value,
t2.Name,
t3.Item

I have following Data in a temp table.

|  Item |  Name  | Value |
--------------------------
| item1 | Name 1 |   2   |
| item2 | Name 1 |   4   |
| item3 | Name 1 |   5   |
| item1 | Name 2 |   6   |
| item2 | Name 2 |   3   |
| item3 | Name 2 |   1   |
| item1 | Name 3 |   7   |
| item2 | Name 3 |   4   |
| item3 | Name 3 |   2   |

I want Name 1, Name 2, Name 3 as column and their value against the respective item.Data in table is dynamic.There can be any number of Items and any number of Name. For every Name and Item their is a value which can be a single digit number. Items are same for every Name Thanks

I need something like below.

|  Item | Name 1 | Name 2 | Name 3|
------------------------------------
| item1 |    2   |    6   |   7   |
| item2 |    4   |    3   |   4   |
| item3 |    5   |    1   |   2   |

Answer

This Worked well for me. 🙂

IF OBJECT_ID('TEMPDB.dbo.##FinalResult ') IS NOT NULL DROP TABLE ##FinalResult 

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME([Name])     
FROM (SELECT DISTINCT [Name] FROM #Result) AS ##FinalResult 

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
  N'SELECT [Item], ' + @ColumnName + '
    INTO ##FinalResult 
    FROM #Result 
    PIVOT(MAX([Value])
    FOR [Name] IN (' + @ColumnName + ')) AS PVTTable'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

SELECT * FROM ##FinalResult