Convert name-value-pair table into a single-row ta

Perhaps the xml functions would help with this?

I couldn't find any example that wasn't hard coding the column names. I got this far:

CREATE PROC dbo.OneRowProc
(
    @Id DECIMAL(15,0)
)
AS BEGIN

    SELECT * 
    into #NameValuePair
    from NameValueTableFunction(@Id)

    DECLARE @Sql VARCHAR(MAX) = 'SELECT '
    UPDATE  #NameValuePair
    SET     @Sql = @Sql + '''' + Value + ''' AS [' + Name + '],'

    SET     @Sql = SUBSTRING(@Sql, 1, LEN(@Sql) - 1)
    EXEC    (@Sql)

END
GO

But, unfortunately, the results of a proc, you can't get it into a temp table b/c you can't select * into #temptable from myproc

DATA

#NameValuePair would look like:

Name        Value
----        -----
Color       Red
Age         43
Mood        Happy

Results would look like:

Color Age Mood
----- --- -----
Red   43  Happy

UPDATE

I'm able now to get it into a temp table, but I still can't get it into a table valued function:

IF OBJECT_ID('tempdb..#PolicyInformation') IS NOT NULL
  DROP TABLE #PolicyInformation
SELECT
  * INTO #PolicyInformation
FROM PolicyInformation(932774264229946)/*60272329046394*/
DECLARE @ColumnDefs AS varchar(max)

--Get distinct values of the PIVOT Column 
SELECT
  @ColumnDefs = ISNULL(@ColumnDefs + ' VARCHAR(MAX),', '')
  + QUOTENAME(Name)
FROM (SELECT
  *
FROM #PolicyInformation) T
SET @ColumnDefs = @ColumnDefs + ' VARCHAR(MAX)'
PRINT @ColumnDefs
IF OBJECT_ID('tempdb..#PolicyInformationTable') IS NOT NULL
  DROP TABLE #PolicyInformationTable
CREATE TABLE #PolicyInformationTable (
  Dummy int
)
DECLARE @Columns varchar(max),
        @AlterTable varchar(max)
SET @AlterTable = 'ALTER TABLE #PolicyInformationTable ADD ' + @ColumnDefs
EXEC (@AlterTable)
ALTER TABLE #PolicyInformationTable DROP COLUMN Dummy
SELECT
  *
FROM #PolicyInformationTable
SELECT
  @Columns = ISNULL(@Columns + ',', '')
  + '''' + Value + ''' as ' + QUOTENAME(Name)
FROM #PolicyInformation
PRINT @Columns
EXEC ('INSERT #PolicyInformationTable SELECT ' + @Columns)
SELECT
  *
FROM #PolicyInformationTable

Using the reference provided, here's how you could accomplisht hat

if object_id('tempdb..#tempValuePair') is not null drop table #tempValuePair
select
'Color' as Name,
'Red' as Value
into #tempValuePair
union all
select
'Age','43'
union all
select
'Mood','Happy'



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 #tempValuePair) AS Courses

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