给定两个示例表:% _' a3 d2 R, ~+ Y7 @ 门票表/ F/ r. M/ ]- t9 w0 E: \# I
ID User Description0 James This is a support ticket1 Fred This is a ticket too属性表 1 T8 }2 E7 L! f- gID TicketID Label Value Engineer Scott 1 1 1 Engineer Dale Manu Dell3 3 1 Manu HP OS Windows5 5 1 OS Linux如何获得这样的视图: 6 J' }1 V( Y( }- ]" v5 Z) D& QID User Description Engineer Manu OS1 James This is a support ticket Scott Dell Windows2 Fred This is a ticket too Dale HP Linux需要注意的是,属性表并不总是一样的。有些票证可能有别人没有的属性。 8 g! n9 ~" x8 I- S9 r6 S有可能吗?3 s* s3 b+ k8 X6 f6 y/ [
9 W! t/ d D! H+ G( n/ b, | 解决方案: 8 |, N/ ^7 J# c" a A1 \6 N6 f) x
您可以使用PIVOT执行此操作PIVOT时,可以使用以下两种方法之一进行操作:使用“静态数据透视表”(将对行进行编码)或“动态数据透视表”将在运行时创建列列表:9 H8 m9 z% b/ N$ M$ v4 E! q- j
静态数据透视表(请参见SQL Fiddle中的Demo): 7 D) P# ~, o' S6 \% H3 c6 }- bselect 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) xpivot( min(value) for label in ([engineer],[manu],[OS])) p或者,你可以用Dynamic Pivot(请参见SQL Fiddle for : D$ F7 I$ ]% I$ [# c! zDemo):* w: P1 C' j! Z, Z: U5 P
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、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)这两个查询将返回相同的结果。