SQL:具有基于源表中列值的列名的动态视图

本文介绍了SQL:具有基于源表中列值的列名的动态视图的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

鉴于这里的两个示例表:

Given the two sample tables here:

门票表

ID User Description 0 James This is a support ticket 1 Fred This is a ticket too

属性表

ID TicketID Label Value 0 0 Engineer Scott 1 1 Engineer Dale 2 0 Manu Dell 3 1 Manu HP 4 0 OS Windows 5 1 OS Linux

我怎样才能到达这样的视图:

How can I arrive at a view like this:

ID User Description Engineer Manu OS 1 James This is a support ticket Scott Dell Windows 2 Fred This is a ticket too Dale HP Linux

需要注意的是，属性表并不总是相同的.一些票"可能具有其他人没有的属性.

It is important to note that the properties table would not always be the same. Some "Tickets" may have properties that others do not.

这甚至可能吗?

推荐答案

您可以使用 枢轴.在执行 PIVOT 时，您可以通过以下两种方式之一进行操作:使用静态 Pivot 对要转换的行进行编码，或者使用 Dynamic Pivot 将在运行时创建列列表:

You can perform this with a PIVOT. When doing the PIVOT you can do it one of two ways, with a Static Pivot that you will code the rows to transform or a Dynamic Pivot which will create the list of columns at run-time:

Static Pivot(参见 SQL Fiddle for Demo):

Static Pivot (See SQL Fiddle for Demo):

select id, [user], [engineer], [manu], [OS] from ( select t.id , t.[user] , p.ticketid , p.label , p.value from tickets t inner join properties p on t.id = p.ticketid ) x pivot ( min(value) for label in ([engineer], [manu], [OS]) ) p

或者您可以使用动态 Pivot(请参阅 SQL Fiddle for Demo):

Or you can use a Dynamic Pivot (See SQL Fiddle for Demo):

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX); select @cols = STUFF((SELECT distinct ',' + QUOTENAME(p.label) from tickets t inner join properties p on t.id = p.ticketid FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT id, [user], ' + @cols + ' from ( select t.id , t.[user] , p.ticketid , p.label , p.value from tickets t inner join properties p on t.id = p.ticketid ) x pivot ( min(value) for label in (' + @cols + ') ) p ' execute(@query)

两个查询将返回相同的结果.

Both query will return the same results.