时间戳

首页 > 解决方案 > 如何将行转换为列 - 复杂的枢轴

sql - 如何将行转换为列 - 复杂的枢轴

问题描述

如何使用动态 SQL 构造返回以下列的数据集。

美联储姓名，美联储地址，首席财务官 1 姓名，首席财务官 1 电子邮件，首席财务官 1 电话，首席财务官 2 姓名，首席财务官 2 电子邮件，首席财务官 2 电话，... 主管 1 姓名，主管 1 电子邮件，主管 1 电话...至 5，总统 1 名等。全部排成一行。

我的数据库中有四个表：

接触：

X18_Digit_Contact_ID__c     |    Name          |    Email    |     Phone     |   Federation_ID
------------------------------------------------------------------------------------------
1                           |    John Smith    | x9@gmail.com |  546-5464889 |     1
2                           |    David smith   | x8@gmail.com |  145-5464889 |     1
3                           |    Ellen Smith   | x9@gmail.com |  546-5464889 |     1
4                           |    Dan smith     | x8@gmail.com |  145-5464889 |     1
5                           |    Kelly Smith   | x9@gmail.com |  546-5464889 |     2
6                           |    Dori smith    | x8@gmail.com |  145-5464889 |     2
7                           |    Nir Smith     | x9@gmail.com |  546-5464889 |     2
8                           |    Victor smith  | x8@gmail.com |  145-5464889 |     2

联系：

X18_Digit_Contact_ID__c     |    npe5__Role__c      
-----------------------------------------------
4                           |    President                  
8                           |    President

联邦：

Federation_ID   |  Name    |   Address
---------------------------------------
1               |   x      |   123 hello 
2               |   y      |   345 world

选区：

X18_Digit_Contact_ID__c     |    Constituency       
-----------------------------------------------
1                           |    CFO                
2                           |    CFO            
3                           |    Director               
7                           |    Director 
5                           |    Director               
6                           |    Director

联盟 1 的预期结果：

  Name    |   Address   |    CFO 1        | CFO 1 Email  | CFO 1 Phone  | CFO 2        | CFO 2 Email  | CFO 2 Phone  | Director 1  | Director 1 Email | Director 1 Phone | President 1 | President 1 Email | President 1 Phone| 
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   x      |   123 hello |    John Smith   | x9@gmail.com | 546-5464889  | David smith  | x8@gmail.com | 145-5464889  | Ellen Smith | x9@gmail.com     | 546-5464889      | Dan smith   | x8@gmail.com      | 145-5464889

每个联合会可以有多个联系人，具有相同的选区和隶属关系。

例如，一个联盟“x”可以有多个 CFO 和多个董事。这些信息来自选区表。（这些数字是动态的。）

那么同一个联合会可以有不止一个主席，这个信息来自从属关系表。

这是我开始的方式，但它并不完全符合我的预期。

DECLARE @query VARCHAR(MAX)

DECLARE @pickedConstituenciesByUser varchar(MAX) = 'Chief Financial Officers,President,Executive Directors'
DECLARE @constituencies VARCHAR(MAX)
SELECT  @constituencies = STUFF (
                            (   
                                SELECT DISTINCT '],[' + Constituency__c

                                FROM    Constituency 

                                WHERE   Active__c = 1 AND Constituency__c IN (SELECT Value FROM fnc_Split_IDs(@pickedConstituenciesByUser, ','))

                                ORDER BY '],[' + Constituency__c

                                FOR XML PATH('')
                            ), 1, 2, ''
                          ) + ']'

--return 
SET @query =  
    'SELECT * FROM ' +
    '( ' +
        'SELECT  c.Name, ' +                                            
                'f.Community, ' +
                'f.CitySize, '+
                'f.CommunityCode, ' +                               
                'f.BillingStreet, ' +
                'f.BillingCity, ' +
                'f.BillingState, ' +
                'f.BillingPostalCode, ' +
                'f.BillingCountry, ' +          
                'ct.Constituency__c ' +             

        'FROM Federation f ' +

        'JOIN Contact c ON f.Account_ID = c.AccountId ' +

        'JOIN Affiliation a ON  c.X18_Digit_Contact_ID__c = a.npe5__Contact__c  ' + 
                                'AND ' +
                                'a.Affiliation_Type__c IN (''Professional'',''Lay'') ' +                                                                

        'JOIN Constituency ct ON ct.Contact__c = c.X18_Digit_Contact_ID__c AND ct.Active__c = 1 ' +         

    ') AS t ' +     

    'PIVOT (MAX(t.Name) ' + 
           'FOR t.Constituency__c IN ('+ @constituencies +')) AS pvt ' +    

    'Order BY CitySize, Community ' 

EXECUTE (@query)

此代码仅返回一位 CFO 和一位董事，没有总裁。

标签： sqlsql-serverdynamic-sql

解决方案

评论太长了。当我们在寻找可消耗的数据时，帮助您使用它的人应该很容易。以下是应如何发布此信息，以便其他人可以对其进行查询。

create table Contact
(
    X18_Digit_Contact_ID__c int
    , Name varchar(50)
    , Email varchar(50)
    , Phone varchar(15)
    , Federation_ID int
)

insert Contact values
(1, 'John Smith', 'x9@gmail.com', '546-5464889', 1)
, (2, 'David smith', 'x8@gmail.com', '145-5464889', 1)
, (3, 'Ellen Smith', 'x9@gmail.com', '546-5464889', 1)
, (4, 'Dan smith', 'x8@gmail.com', '145-5464889', 1)
, (5, 'Kelly Smith', 'x9@gmail.com', '546-5464889', 2)
, (6, 'Dori smith', 'x8@gmail.com', '145-5464889', 2)
, (7, 'Nir Smith', 'x9@gmail.com', '546-5464889', 2)
, (8, 'Victor smith', 'x8@gmail.com', '145-5464889', 2)

create table Affiliation
(
    X18_Digit_Contact_ID__c int
    , npe5__Role__c varchar(20)
)

insert Affiliation values
(4, 'President')                 
, (8, 'President')

create table Federation
(
    Federation_ID int
    , Name varchar(10)
    , Address varchar(20)
)

insert Federation values
(1, 'x', '123 hello')
, (2, 'y', '345 world')

create table Constituency
(
    X18_Digit_Contact_ID__c int
    , Constituency varchar(10)
)

insert Constituency values
(1, 'CFO')
, (2, 'CFO')
, (3, 'Director')
, (7, 'Director')
, (5, 'Director')
, (6, 'Director')

我今天没有更多的时间来帮助你。也许其他人将能够使用我创建的数据来提供帮助。

推荐阅读