如何透视未知数的列& SQL Server中没有聚合? [英] How to pivot unknown number of columns & no aggregate in SQL Server?

问题描述

我有一个查询，该查询返回具有相关抵押名称的客户贷款，如下所示(1) 但我想像其他示例(2)那样，仅连续保留一个不同的贷款编号和抵押名称.一直在进行透视，但由于我没有汇总列，也不知道要解决的原因，我也不知道每笔贷款可能没有多少抵押品.怎么做???可以在SQL Server 2012中使用吗?

I have query which returns clients loans with associated collateral names like below (1) but I want to have only one distinct loan number in a row and collateral names aside like on other example (2). Been playing with pivoting but cannot figure it out because I don't have aggregate column and I don't know how many loan numbers I will get neither how many collateral each loan may have. How to do that??? Possible in 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)我需要这样的东西

(2) I need something like this

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

推荐答案

测试数据

DECLARE @TABLE TABLE (loanid INT,name VARCHAR(20),[Address] VARCHAR(20))
INSERT INTO @TABLE VALUES
(1,'John','New York'),(1,'Carl','New York'),(1,'Henry','Boston'),
(2,'Robert','Chicago'),(3,'Joanne','LA'),(3,'Chris','LA')

查询

SELECT  loanid
       ,ISNULL(name1, '')    AS name1
       ,ISNULL(Address1, '') AS Address1
       ,ISNULL(name2, '')    AS name2
       ,ISNULL(Address2, '') AS Address2
       ,ISNULL(name3, '')    AS name3
       ,ISNULL(Address3, '') AS Address3
FROM (
SELECT loanid
      ,'name' + CAST(ROW_NUMBER() OVER (PARTITION BY loanid ORDER BY loanid)  AS NVARCHAR(10)) AS Cols
      , name AS Vals
FROM @TABLE
UNION ALL
SELECT loanid
      ,'Address' + CAST(ROW_NUMBER() OVER (PARTITION BY loanid ORDER BY loanid)  AS NVARCHAR(10)) 
      , [Address] 
FROM @TABLE ) t
 PIVOT (MAX(Vals)
        FOR Cols 
        IN (name1, Address1,name2,Address2,name3,Address3)
        )P

结果集

╔════════╦════════╦══════════╦═══════╦══════════╦═══════╦══════════╗
║ loanid ║ name1  ║ Address1 ║ name2 ║ Address2 ║ name3 ║ Address3 ║
╠════════╬════════╬══════════╬═══════╬══════════╬═══════╬══════════╣
║      1 ║ John   ║ New York ║ Carl  ║ New York ║ Henry ║ Boston   ║
║      2 ║ Robert ║ Chicago  ║       ║          ║       ║          ║
║      3 ║ Joanne ║ LA       ║ Chris ║ LA       ║       ║          ║
╚════════╩════════╩══════════╩═══════╩══════════╩═══════╩══════════╝

更新动态列

DECLARE @Cols NVARCHAR(MAX);

SELECT @Cols =  STUFF((
                    SELECT DISTINCT ', ' +  QUOTENAME(Cols)
                    FROM (
                    SELECT loanid
                          ,'name' + CAST(ROW_NUMBER() OVER (PARTITION BY loanid ORDER BY loanid)  AS NVARCHAR(10)) AS Cols
                          , name AS Vals
                    FROM @TABLE
                    UNION ALL
                    SELECT loanid
                          ,'Address' + CAST(ROW_NUMBER() OVER (PARTITION BY loanid ORDER BY loanid)  AS NVARCHAR(10)) 
                          , [Address] 
                    FROM @TABLE ) t
                    GROUP BY QUOTENAME(Cols)
                    FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,2,'')


DECLARE @Sql NVARCHAR(MAX);

SET @Sql  = 'SELECT ' + @Cols +   '
            FROM (
            SELECT loanid
                  ,''name'' + CAST(ROW_NUMBER() OVER 
                            (PARTITION BY loanid ORDER BY loanid)  AS NVARCHAR(10)) AS Cols
                  , name AS Vals
            FROM @TABLE
            UNION ALL
            SELECT loanid
                  ,''Address'' + CAST(ROW_NUMBER() OVER 
                                (PARTITION BY loanid ORDER BY loanid)  AS NVARCHAR(10)) 
                  , [Address] 
            FROM @TABLE ) t
             PIVOT (MAX(Vals)
                    FOR Cols 
                    IN (' + @Cols + ')
                    )P'

EXECUTE sp_executesql @Sql

注意

在我的答案中，这将无法使用给定的示例数据，因为它使用表变量，并且由于它具有自己的作用域，因此对于动态sql不可见.但是此解决方案可以在普通的sql server表上使用.

This wouldnt work with the given sample data in my answer, as it uses a table variable and it is not visible to dynamic sql since it has it own scope. but this solution will work on a normal sql server table.

另外，选择列的顺序也会略有不同.

Also the order in which columns are selected will be slightly different.

这篇关于如何透视未知数的列& SQL Server中没有聚合?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！