Converting row data to dynamic columns

Question

I know similar questions have been asked before but I am stuck in the weeds and I need help. I have a data set that looks similar to this

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

I need to transform the data like this in SQL. The dependent columns should be dynamic.

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

Any help would be great.

What have you tried so far, or what about the questions you've seen didn't you understand (and what were those questions)? — Thom A, Nov 04 '20 at 15:14
Hi, so here is one of the questions that I looked at with the solutions but it did not help. https://stackoverflow.com/questions/18116020/sql-server-2008-vertical-data-to-horizontal this is because the column names are not in the data set and so the First and last names of the dependents would have to be the column names which is not what I need. I hope this answers your question. — vfield, Nov 04 '20 at 15:24

critical_error · Answer 1 · 2020-11-04T20:37:19.087

There are not many easy ways to go about what you're asking without resorting to the use of dynamic SQL; however, here is one way you might try.

Note: I've made some assumptions about your data, namely, that badgenum is a value that ties dependents to employees.

/* 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;

Returns

+-----------+-----------+-------+---------------+---------------+---------------+---------------+---------------+---------------+
| 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          |
+-----------+-----------+-------+---------------+---------------+---------------+---------------+---------------+---------------+

@vfield You're welcome. Please accept as the answer if this addressed your inquiry. — critical_error, Nov 05 '20 at 16:38

Converting row data to dynamic columns

1 Answers1