SQL Sending values where code matches another table

Question

I have a table with data records:

T1:

Example T1 Data:

Unique_id   RatingA   RatingB   RatingC
    123       1          3       Null
    456      -1        Null       2

I have another table with a code that equates to the ratings,

T2:

 Rating_Name  Rating_Code
   RatingA        11
   RatingB        21
   RatingC        31

I need to send the Unique_id from T1, rating codes from T2 with the Rating values from T1.

Desired output

My export file should look like this, taking the Rating from the corresponding column (A, B or C):

Unique_id  Rating_Code  Rating
   123           11        1
   123           21        3
   123           31      (null)
   456           11       -1
   456           21      (null)
   456           31        2

How do I do this?

Will there only ever be three Ratings (A, B, C)? Or can further ratings (D, E, etc.) be added over time? — Philip Kelley, Feb 02 '16 at 21:14
There are hundreds of ratings, I was simplifying my example. — Mrodonis, Feb 03 '16 at 16:49

trincot · Answer 1 · 2016-02-04T13:45:26.247

You could you a CASE WHEN clause to unpivot the three columns for RatingA, B and C:

SELECT      T1.Unique_id,
            T2.Rating_Code,
            CASE Rating_Name
                WHEN 'RatingA' THEN T1.RatingA
                WHEN 'RatingB' THEN T1.RatingB
                WHEN 'RatingC' THEN T1.RatingC
            END AS Rating
FROM        T1
CROSS JOIN  T2

Output:

+-----------+-------------+--------+
| Unique_id | Rating_Code | Rating |
+-----------+-------------+--------+
|    123    |       11    |    1   |
|    123    |       21    |    3   |
|    123    |       31    | (null) |
|    456    |       11    |   -1   |
|    456    |       21    | (null) |
|    456    |       31    |    2   |
+-----------+-------------+--------+

SQL Fiddle.

If you have other records in T2 than the three given in your question, then add a WHERE clause at the end of the statement:

WHERE       Rating_Name IN ('RatingA', 'RatingB', 'RatingC');

Addendum

As you have indicated you have in fact many (hundreds) of such RatingXXX columns, it must be said that this is a bad database design. In fact, the output you want to generate should be a proper table that you would keep updated instead of these columns.

Anyway, to help you produce the above SQL without having to type those hundreds of column names, you could use this:

SELECT      concat('WHEN ''', Rating_Name, ''' THEN T1.', Rating_Name)
FROM        T2

This would output something like:

WHEN 'RatingA' THEN T1.RatingA
WHEN 'RatingB' THEN T1.RatingB
WHEN 'RatingC' THEN T1.RatingC

... which you can copy into your final SQL's CASE clause. This assumes of course that the rating name in your table T2 is the exact column name in T1.

That worked beautifully. Although there are hundreds of records. Any statements that may select them into the CASE statement vs manually typing them all? @trincot, thanks so much for reformatting my original post. It's my first time using stack overflow & I appreciate the help! — Mrodonis, Feb 03 '16 at 17:24
Glad you like it. Are you saying your T1 table has hundreds of RatingXXX columns? — trincot, Feb 03 '16 at 19:19

shawnt00 · Answer 2 · 2016-02-02T21:12:35.557

select Unique_id, Rating_Code, Rating
from
    T1 cross apply
    (values
        ('RatingA', RatingA),
        ('RatingB', RatingB),
        ('RatingC', RatingC)
    ) pvt(Rating_Name, Rating)
    inner join T2
        on T2.Rating_Name = pvt.Rating_Name

This is very similar to @trincot's answer. One small different is that I make no assumption that you want all the values from T2. It's not clear whether that's correct now or might be incorrect in the future. In @trincot's favor it's possible, though not likely, that you don't have cross apply available on your version of SQL Server.

SQL Sending values where code matches another table

T1:

T2:

Desired output

2 Answers2

Addendum