将名称 - 值对表转换为名称为列的单行表，而不对列名进行硬编码

Question

也许xml函数对此有帮助？

我找不到任何不难编码列名的例子。 我能走到今天：

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 

但不幸的是，一个进程的结果，你不能让它到一个临时表B/C，你不能select * into #temptable from myproc

DATA

#NameValuePair会是什么样子：

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

结果会是什么样子：

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

UPDATE

我现在能够得到它到一个临时表，但我仍然无法得到它到一个表值函数：

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 

Answer 1

使用提供参考，下面是你如何能accomplisht帽子

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