sql - 在 SQL Server 中动态地将行转换为列

Question

我想将下面的行动态转换为列。

**Process Id     Attribute    Values**
1              Equipment     Normal
1              Complaints    No
1              Availability  30 min
2              Phone1        123456789
2              Phone2        987654321

我试图改变它，但我无法得到想要的结果。我需要以下输出

Process ID     Attribute1     Value1    Arrtibute2     Value2     Attribute3     Value3
1              Equipment     Normal       Complaints    No            Availability  30 min
2              Phone1        123456789    Phone2        987654321     NULL          NULL

一个进程可以有一个或多个属性。因此，如果一个进程有 10 个属性，它应该在所需的输出中创建 10 列。有什么建议么？

标签： sqlsql-serversql-server-2012

Answer 1

试试下面的代码。它将提供所需的输出。

My sample Algorithm
 1. Create manual named columns by using ROW_NUMBER()
 2. Create Dynamic columns for Attribute,Value named as @columnsAttribute, @columnsValue
 3. Create Dynamic group by columns for both Attribute & Value named as @Allcolumns
 4. Dynamic query creation with pivot for both Attribute & Value

declare @tblAttribute as 
    table(processid int, 
    attribute NVARCHAR(100),
    value NVARCHAR(200))

insert into @tblAttribute(processid,attribute,value) 
values(1,'Equipment','Normal'),
        (1,'Complaints','No'),
        (1,'Availability','30 min'),
        (1,'test','testvalue'),
        (2,'Phone1','123456789'),
        (2,'Phone2','987654321')


;with ctetbl as
(
select ROW_NUMBER() over (partition by processid order by processid) rno,* from @tblAttribute
) 
,ctetbl1 as 
(
    select processid,
    'Attribute'+ cast(rno as NVARCHAR(2)) as DynamicAttribute,
    'Value'+cast(rno as NVARCHAR(2)) as DyanamicValue
    ,attribute,value from ctetbl
)
select * into #tblDynamicAttribute from ctetbl1

declare @Allcolumns as NVARCHAR(max),
        @columnsAttribute as NVARCHAR(max),
        @columnsValue as NVARCHAR(max),
        @sql as NVARCHAR(MAX) = ''


select @Allcolumns=coalesce(@Allcolumns+',','')+'max('+QUOTENAME(B.DynamicAttribute)+') as '+ QUOTENAME(B.DynamicAttribute) +',max('+QUOTENAME(B.DyanamicValue)+') as '+ QUOTENAME(B.DyanamicValue)
from (select distinct DynamicAttribute,DyanamicValue from #tblDynamicAttribute) as B
order by b.DynamicAttribute

select @columnsAttribute=coalesce(@columnsAttribute+',','')+QUOTENAME(B.DynamicAttribute)
from (select distinct DynamicAttribute,DyanamicValue from #tblDynamicAttribute) as B
order by b.DynamicAttribute

select @columnsValue=coalesce(@columnsValue+',','')+QUOTENAME(B.DyanamicValue)
from (select distinct DynamicAttribute,DyanamicValue from #tblDynamicAttribute) as B
order by b.DynamicAttribute


-- construct dynamic SQL
SET @sql ='
select x.processid,' + @Allcolumns +' from (
SELECT processid,' + @columnsAttribute+','+ @columnsValue +' FROM   
(
    SELECT 
        processid,attribute,value,dynamicattribute,DyanamicValue
    FROM 
        #tblDynamicAttribute p
) t 
PIVOT(
    max(attribute) 
    FOR dynamicattribute IN ('+ @columnsAttribute +')
) AS pivot_table
PIVOT(
    max(value) 
    FOR DyanamicValue IN ('+ @columnsValue +')
) AS pivot_table1

) x group by processid;';

-- execute the dynamic SQL
EXECUTE sp_executesql @sql;

drop table #tblDynamicAttribute

样本输出