Query to produce a table with 7 columns based on the values in a two column table

Transact-SQL
1

I need a query that will 'Select' 6 columns named MN, S1, S2, S3, S4, S5 where the value in MN is derived from column A's value where column B's value is -1 and the value in columns S1-S5 are derived from column A's value where column B's value is the value of MN. Every row should have the 7 columns. Those that do not produce a value should be 'NULL'. You can assume the order in the table is exactly as the order of the columns in the output.

This is an example of a table
A B
10 -1
50 10
51 10
52 10
11 -1
60 11
61 11
62 11
63 11
12 -1
70 12
71 12
72 12
73 12
74 12

This is how the output should look
MN S1 S2 S3 S4 S5
10 50 51 52
11 60 61 62 63
12 70 71 72 73 74

2

--set up sample data
CREATE TABLE #data (
A int NULL,
B int NULL
)
INSERT INTO #data VALUES
(10, -1),(50, 10),(51, 10),(52, 10),
(11, -1),(60, 11),(61, 11),(62, 11),(63, 11),
(12, -1),(70, 12),(71, 12),(72, 12),(73, 12),(74, 12)

--do the actual query
SELECT 
    MN,
    MAX(CASE WHEN row_num = 1 THEN S END) AS S1,
    MAX(CASE WHEN row_num = 2 THEN S END) AS S2,
    MAX(CASE WHEN row_num = 3 THEN S END) AS S3,
    MAX(CASE WHEN row_num = 4 THEN S END) AS S4,
    MAX(CASE WHEN row_num = 5 THEN S END) AS S5
FROM (
    SELECT d_MN.A AS MN, d_s.A AS S, 
        ROW_NUMBER() OVER(PARTITION BY d_MN.A ORDER BY d_S.A) AS row_num
    FROM #data d_MN
    INNER JOIN #data d_S ON d_S.B = d_MN.A
    WHERE d_MN.B = -1
) AS query1
GROUP BY MN
ORDER BY MN

You can assume the order in the table is exactly as the order of the columns in the output.

Only if there's an ORDER BY statement; otherwise, the order of rows returned by a SELECT is never guaranteed in SQL Server.

2 Likes
3

Great job! Worked perfectly. Now I just have to adapt it to the real data. :slight_smile: