SQL Server行到列 [英] SQL Server rows to columns

问题描述

我的查询是:

SELECT vendor.id, insurances.id AS ins_id, vendor_insurances.expiry_date
FROM vendor
INNER JOIN vendor_insurances
ON vendor.id=vendor_insurances.vendor_id

和输出:

id    ins_id    expiry_date
================================
28    1         2006-01-01
28    11        2008-01-01

我想将其转换为:

id    1            11
======================================
28    2006-01-01   2008-01-01

谢谢

推荐答案

您将需要使用

You will need to use PIVOT and do something similar to this:

静态数据透视-仅需透视几列

Static Pivot - for just a few number of columns that you will pivot

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

有关工作演示，请参见 SQL小提琴

See SQL Fiddle for Working Demo

或者，如果您有很多要放置PIVOT的项目，则可以使用动态枢轴":

Or you can use a Dynamic Pivot if you have a lot of items to 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)

两者都会给您相同的结果.

Both will give you the same results.

这篇关于SQL Server行到列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！