当前位置: 首页 > 面试题库 >

如何在SQL Server中透视未知的列数且没有聚合?

蓝星辰
2023-03-14
问题内容

我有一个查询,该查询返回具有相关抵押名称的客户贷款,如以下(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|

问题答案:

测试数据

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表上使用。

同样,选择列的顺序也会略有不同。



 类似资料:
  • 我使用Apache Spark 2.2.0和Scala。 我以这个问题为指导,在不使用pivot函数的情况下透视数据帧。 我需要在不使用pivot函数的情况下透视数据帧,因为我有非数字数据,而< code>pivot只对数字数据使用聚合函数,如< code>sum 、< code>min 、< code>max。我想在< code>pivot聚合中使用一个非数字列。 这是我的数据: 我希望它按<

  • 问题内容: 我需要对表中的XML列执行数据透视,其中XML包含具有多个属性的多个元素。每个元素中的属性始终相同,但是元素的数量会有所不同。让我举个例子吧… 我需要为每个不同的FieldName属性值(在此示例中为Username,FirstName,LastName和NewField)使用相应的FieldValue属性作为值的结果集结束。我上面给出的示例的结果如下所示: 我想出了一种使用静态列来完

  • 问题内容: 我在下面的表A中,对于每个唯一的ID,都有三个带有某些值的代码。 我有第二个表B,其格式如下: 每个唯一ID仅一行。我想为第一个表中的每个ID填充第一个表A中的第二个表B。 对于上面的第一个表A,第二个表B应该显示为: 如何在单个SQL查询中实现此目标? 问题答案: 如果您的版本没有,您也可以使用以下命令: (在我的V6R1 DB2实例上工作,并有一个SQL Fiddle示例)。

  • 什么是透视? 如何透视? 这是枢轴吗? 长格式到宽格式? 我见过很多关于透视表的问题。即使他们不知道他们询问的是透视表,他们通常也是。几乎不可能写出一个包含旋转的所有方面的规范的问题和答案。 ...但我要试一试。 现有问题和答案的问题是,问题通常集中在一个细微差别上,而OP很难将其概括出来,以便使用现有的许多好答案。然而,没有一个答案试图给出一个全面的解释(因为这是一个令人生畏的任务) 从我的谷歌

  • 我试图通过将数据中的一列转换为行(通过旋转或展开)来重塑pandas dataframe。

  • 我的步骤有以下FlatFileItemReader配置: RawInput包含1个字段,因此它允许我用单列读取csv。现在的要求是变化,现在我必须能够读取任何csv文件,任何行数,因此我需要以某种方式传递数组,而不是RAW输入。是否可以使用FlatFileItemReader,或者我应该更改实现?