SQL 将行转换为列并填充值 [英] 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

我需要帮助的是执行以下操作:对于每个名称，将 type_name 作为一列，然后用 default_value 或 value 填充这些列.

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.

像这样:

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

基本上从 default_value 或 value 填充，具体取决于哪个不为 null，如果两者都为零，则为 0.

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

任何人都可以帮我解决这个问题，因为我这里缺乏 SQL 知识.

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

非常感谢！

推荐答案

您可以使用PIVOT如下:

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

干杯！！

这篇关于SQL 将行转换为列并填充值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！