SQL turning rows into columns and populating with values

I’m not quite sure why this table was designed this way, but it’s making it hard to solve my problem. Looking at the data:

NAME            TYPE_NAME               DEFAULT_VALUE           VALUE
TEST 1          Currency                Null                    14
TEST 1          Event Count             0                       0
TEST 1          Usage                   8                       Null
TEST 1          Events Amt              0                       0
TEST 1          Usage Amt               Null                    13
TEST 1          From Date               Null                    5
TEST 1          To Date                 6                       Null
TEST 1          Traffic Scenario        Null                    2
TEST 1          Band                    1                       Null
TEST 1          Service                 15                      Null
TEST 1          Tariff Rate Name        Null                    4
TEST 2          Currency                EUR                     0
TEST 2          Event Count             Null                    9
TEST 2          Usage                   10                      Null
TEST 2          Events Amt              Null                    13
TEST 2          Usage Amt               Null                    14
TEST 2          From Date               Null                    3
TEST 2          To Date                 4                       Null
TEST 2          Traffic Scenario        Null                    5
TEST 2          Band                    6                       Null
TEST 2          Service                 7                       Null
TEST 2          Tariff Rate Name        Null                    8
TEST 3          Currency                USD                     0
TEST 3          Event Count             0                       0
TEST 3          Usage                   4                       Null
TEST 3          Events Amt              0                       0
TEST 3          Usage Amt               Null                    5
TEST 3          From Date               Null                    1
TEST 3          To Date                 2                       Null
TEST 3          Traffic Scenario        13603                   0
TEST 3          Band                    3                       Null
TEST 3          Service                 3                       Null
TEST 3          Tariff Rate Name        24HR                    0

What I need help with is to do the following: For each name have the type_name as a column and then fill those columns with the default_value or value.

Like this:

NAME    Currency        Event Count     Usage   Events Amt      Usage Amt       From Date       To Date Traffic Scenario        Band    Service Tariff Rate Name
TEST 1  14              0               8       0               13              5               6       2                       1       15      4
TEST 2  EUR             9               10      13              14              3               4       5                       6       7       8
TEST 3  USD             0               4       0               5               1               2       13603                   3       3       24HR

Basically populates from default_value or value depending which one is not null, if both zero then just 0.

Could anyone help me with this as my SQL knowledge is lacking here.

Many thanks!

Answer

You can use PIVOT as follows:

SELECT * FROM
( SELECT NAME, TYPE_NAME, COALESCE(VALUE, DEFAULT_VALUE) AS VAL
    FROM YOUR_TABLE
) 
PIVOT 
( MAX ( VAL )
  FOR TYPE_NAME IN ( 'Currency' AS CURRENCY, 'Event Count' AS EVENT_COUNT, 
                       'Usage' AS USAGE_, 'Events Amt' AS EVENTS_AMT )
)

Cheers!!

Advertisement

Answer