1

I have a table like this:

╔═════╦═══════╦════╦════╦═════╦═════╦═════╦═════╦════╦════╗
║ ZIP ║ STATE ║ F  ║ C  ║ F1  ║ c1  ║ f2  ║ c2  ║ f3 ║ c3 ║
╠═════╬═══════╬════╬════╬═════╬═════╬═════╬═════╬════╬════╣
║ A   ║ AS    ║ AF ║ AC ║ AF1 ║ AC1 ║ AF2 ║ AC2 ║    ║    ║
║ B   ║ BS    ║ BF ║ BC ║ BF1 ║ BC1 ║     ║     ║    ║    ║
╚═════╩═══════╩════╩════╩═════╩═════╩═════╩═════╩════╩════╝

The output I am looking for is:

╔═════╦═══════╦═════╦═════╗
║ ZIP ║ STATE ║  F  ║  C  ║
╠═════╬═══════╬═════╬═════╣
║ A   ║ AS    ║ AF  ║ AC  ║
║ A   ║ AS    ║ AF1 ║ AC1 ║
║ A   ║ AS    ║ AF2 ║ AC2 ║
║ B   ║ BS    ║ BF  ║ BC  ║
║ B   ║ BS    ║ BF1 ║ BC1 ║
╚═════╩═══════╩═════╩═════╝

How can I achieve this?

3

Given this test data:

DECLARE @T AS TABLE
(
    ZIP sql_variant NOT NULL,
    STATE sql_variant NOT NULL,
    F sql_variant NULL,
    C sql_variant NULL,
    F1 sql_variant NULL,
    C1 sql_variant NULL,
    F2 sql_variant NULL,
    C2 sql_variant NULL,
    F3 sql_variant NULL,
    C3 sql_variant NULL
);

INSERT @T 
    (ZIP, STATE, F, C, F1, C1, F2, C2)
VALUES 
    ('A', 'AS', 'AF', 'AC', 'AF1', 'AC1', 'AF2', 'AC2');

INSERT @T 
    (ZIP, STATE, F, C, F1, C1)
VALUES 
    ('B', 'BS', 'BF', 'BC', 'BF1', 'BC1');

One way to produce the desired output is:

SELECT
    T.ZIP,
    T.STATE,
    CA.F,
    CA.C
FROM @T AS T
CROSS APPLY
(
    VALUES
        (F, C),
        (F1, C1),
        (F2, C2),
        (F3, C3)
) AS CA (F, C)
WHERE
    CA.F IS NOT NULL
    OR CA.C IS NOT NULL;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.