您将需要使用PIVOT http://msdn.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx并做类似的事情:
静态透视 - 仅适用于您将透视的少数列
select *
from
(
SELECT v.id
, vi.id AS ins_id
, vi.expiry_date
FROM vendor v
INNER JOIN vendor_insurances vi
ON v.id=vi.vendorId
) x
PIVOT
(
MIN(expiry_date)
FOR ins_id IN ([1], [11])
) p
See SQL小提琴 http://sqlfiddle.com/#!3/31fca/3用于工作演示
或者,如果您有很多要进行 PIVOT 的项目,则可以使用动态 Pivot:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(vi.id)
FROM vendor v
INNER JOIN vendor_insurances vi
ON v.id=vi.vendorId
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT id, ' + @cols + ' from
(
SELECT v.id
, vi.id AS ins_id
, vi.expiry_date
FROM vendor v
INNER JOIN vendor_insurances vi
ON v.id=vi.vendorId
) x
pivot
(
MIN(expiry_date)
for ins_id in (' + @cols + ')
) p '
execute(@query)
两者都会给你相同的结果。