sql-server - 如何在 SQL Server 中透视未知数量的列且非聚合？

Question

我有一个查询，该查询返回具有相关抵押品名称的客户贷款，如下所示 (1)但我只想连续有一个不同的贷款号码和抵押品名称，就像其他示例 (2) 一样。一直在玩旋转，但无法弄清楚，因为我没有汇总列，而且我不知道我会得到多少贷款号码，也不知道每笔贷款可能有多少抵押品。怎么做？？？在 SQL Server 2012 中可能吗？

谢谢

(1)

loanid|name  |Address |
1     |John  |New York|
1     |Carl  |New York|
1     |Henry |Boston  |
2     |Robert|Chicago |
3     |Joanne|LA      |
3     |Chris |LA      |

(2)我需要这样的东西

loanid|name  |address  |name |address |name|address|
1     |Jonh  |New York |Carl |New York|Henry|Boston|
2     |Robert|Chicago  |
3     |Joanne|LA       |Chris|LA|

Answer 1

同时M.Ali's answer将为您提供结果，因为您使用的是 SQL Server 2012，所以我将稍微不同地对 name 和 address 列进行逆透视以获得最终结果。

由于您使用的是 SQL Server 2012，因此您可以使用 CROSS APPLY 和 VALUES 将这些多列反转为多行。但在执行此操作之前，我将使用 row_number() 来获取您将拥有的新列的总数。

使用 CROSS APPLY 对数据进行“UNPIVOT”的代码如下所示:

select d.loanid, 
  col = c.col + cast(seq as varchar(10)),
  c.value
from
(
  select loanid, name, address,
    row_number() over(partition by loanid
                      order by loanid) seq
  from yourtable
) d
cross apply
(
  values
    ('name', name),
    ('address', address)
) c(col, value);

参见SQL Fiddle with Demo 。这会将您的数据转换为类似于以下的格式:

| LOANID |      COL |    VALUE |
|--------|----------|----------|
|      1 |    name1 |     John |
|      1 | address1 | New York |
|      1 |    name2 |     Carl |
|      1 | address2 | New York |
|      1 |    name3 |    Henry |
|      1 | address3 |   Boston |

您现在拥有单个列 COL，其中包含所有新列名称，并且关联的值也位于单个列中。现在，新列名称末尾有一个数字(1、2、3 等)，具体取决于每个 loanid 的总条目数。现在您可以应用 PIVOT:

select loanid,
  name1, address1, name2, address2,
  name3, address3
from
(
  select d.loanid, 
    col = c.col + cast(seq as varchar(10)),
    c.value
  from
  (
    select loanid, name, address,
      row_number() over(partition by loanid
                        order by loanid) seq
    from yourtable
  ) d
  cross apply
  (
    values
      ('name', name),
      ('address', address)
  ) c(col, value)
) src
pivot
(
  max(value)
  for col in (name1, address1, name2, address2,
              name3, address3)
) piv;

参见SQL Fiddle with Demo 。最后，如果您不知道有多少对 Name 和 Address，那么您可以使用动态 SQL:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(col+cast(seq as varchar(10))) 
                    from 
                    (
                      select row_number() over(partition by loanid
                                                order by loanid) seq
                      from yourtable
                    ) d
                    cross apply
                    (
                      select 'Name', 1 union all
                      select 'Address', 2
                    ) c (col, so)
                    group by seq, col, so
                    order by seq, so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT loanid,' + @cols + ' 
            from 
            (
              select d.loanid, 
                col = c.col + cast(seq as varchar(10)),
                c.value
              from
              (
                select loanid, name, address,
                  row_number() over(partition by loanid
                                    order by loanid) seq
                from yourtable
              ) d
              cross apply
              (
                values
                  (''name'', name),
                  (''address'', address)
              ) c(col, value)
            ) x
            pivot 
            (
                max(value)
                for col in (' + @cols + ')
            ) p '

exec sp_executesql @query;

参见SQL Fiddle with Demo 。两个版本都给出了结果:

| LOANID |  NAME1 | ADDRESS1 |  NAME2 | ADDRESS2 |  NAME3 | ADDRESS3 |
|--------|--------|----------|--------|----------|--------|----------|
|      1 |   John | New York |   Carl | New York |  Henry |   Boston |
|      2 | Robert |  Chicago | (null) |   (null) | (null) |   (null) |
|      3 | Joanne |       LA |  Chris |       LA | (null) |   (null) |