时间戳

首页 > 解决方案 > 将行数据转换为动态列

sql-server - 将行数据转换为动态列

问题描述

我知道以前有人问过类似的问题，但我陷入困境，我需要帮助。我有一个看起来与此类似的数据集

FIRSTNAME  LastNAME  BCODE  Boption           ISDEPENDENT  CRELATIONSHIP  CLASTNAME  CFIRSTNAME  BadgeNum 
BigBird    Street      VIS    Employee+Family  Y           Child          Bert        Street     1234
BigBird    Street      VIS    Employee+Family  Y           Child          Ernie       Street     1234
Oscar      TheGrouch   VIS    EmployeeOnly     N           Null           Null        Null       9090

我需要在 SQL 中像这样转换数据。依赖列应该是动态的。

FIRSTNAME  LASTNAME  BCODE  Dependent 1LN  Dependent 1FN  Dependent 2LN Dependent 2FN  
BigBird    Street     VIS    Street         Bert          Street        Ernie   
Oscar      TheGrouch  VIS     null          null          Null          Null

任何帮助都会很棒。

标签： sql-server

解决方案

如果不使用动态 SQL，没有多少简单的方法可以解决您的问题。但是，这是您可以尝试的一种方法。

注意：我对您的数据做了一些假设，即徽章是一个将家属与员工联系起来的值。

/* Mock-up table and data */

DECLARE @Data table (
    firstname varchar(50), lastname varchar(50), bcode varchar(3), boption varchar(50), isdependent varchar(1), crelationship varchar(10), clastname varchar(50), cfirstname varchar(50), badgenum int
);

INSERT INTO @Data (
    firstname, lastname, bcode, boption, isdependent, crelationship, cfirstname, clastname, badgenum
) VALUES
    ( 'BigBird', 'Street', 'VIS', 'Employee+Family', 'Y', 'Child', 'Bert', 'Street', 1234 ),
    ( 'BigBird', 'Street', 'VIS', 'Employee+Family', 'Y', 'Child', 'Ernie', 'Street', 1234 ),
    ( 'Oscar', 'TheGrouch', 'VIS', 'EmployeeOnly', 'N', NULL, NULL, NULL, 9090 );

/* Select employees with dependents listed linearly */

;WITH emp AS (
    SELECT DISTINCT
        firstname, lastname, bcode, badgenum
    FROM @Data
)
SELECT
    firstname,
    lastname,
    bcode,
    dep.dep_xml.value( 'data(//dependents/dependent[@id="1"]/lname)[1]', 'varchar(50)' ) AS [Dependent 1LN],
    dep.dep_xml.value( 'data(//dependents/dependent[@id="1"]/fname)[1]', 'varchar(50)' ) AS [Dependent 1FN],
    dep.dep_xml.value( 'data(//dependents/dependent[@id="2"]/lname)[1]', 'varchar(50)' ) AS [Dependent 2LN],
    dep.dep_xml.value( 'data(//dependents/dependent[@id="2"]/fname)[1]', 'varchar(50)' ) AS [Dependent 2FN],
    dep.dep_xml.value( 'data(//dependents/dependent[@id="3"]/lname)[1]', 'varchar(50)' ) AS [Dependent 3LN],
    dep.dep_xml.value( 'data(//dependents/dependent[@id="3"]/fname)[1]', 'varchar(50)' ) AS [Dependent 3FN]
    -- etc...
FROM emp
OUTER APPLY (

    SELECT CAST ( ( 
        SELECT
            ROW_NUMBER() OVER ( ORDER BY d.firstname, d.lastname ) AS '@id',
            d.cfirstname AS fname,
            d.clastname AS lname
        FROM @Data d
        WHERE
            d.badgenum = emp.badgenum
            AND d.isdependent = 'Y'
        FOR XML PATH ( 'dependent' ), ROOT ( 'dependents' )
    ) AS xml ) AS dep_xml

) AS dep;

退货

+-----------+-----------+-------+---------------+---------------+---------------+---------------+---------------+---------------+
| firstname | lastname  | bcode | Dependent 1LN | Dependent 1FN | Dependent 2LN | Dependent 2FN | Dependent 3LN | Dependent 3FN |
+-----------+-----------+-------+---------------+---------------+---------------+---------------+---------------+---------------+
| BigBird   | Street    | VIS   | Street        | Bert          | Street        | Ernie         | NULL          | NULL          |
| Oscar     | TheGrouch | VIS   | NULL          | NULL          | NULL          | NULL          | NULL          | NULL          |
+-----------+-----------+-------+---------------+---------------+---------------+---------------+---------------+---------------+

推荐阅读