时间戳

首页 > 解决方案 > SQL ORACLE make result in one row

sql - SQL ORACLE make result in one row

问题描述

I'm using ORACLE 11G , I want to make a query but I dont know how it works , and if is it possible: This is my query :

SELECT  DC.CLEF_NAME,DC.CLEF_X, DC.CLEF_Y, DC.CLEF_COL, DC.CLEF_ROW, DC.CLEF_CLASSCOLOR ,DCO.COLOR_NAME,DCO.COLOR_VALUE
FROM DASHBOARD_SAVE DS 
LEFT JOIN DASHBOARD_CLEF DC ON DC.DASHBOARD_UID = DS.DASHBOARD_UID 
LEFT JOIN DASHBOARD_COLOR DCO ON DCO.CLEF_UID = DC.CLEF_UID
WHERE DC.REGROUPEMENT_UID is null and DS.DASHBOARD_USERID = 'testok'

I got the result : 

TESTDECLEF  1   1   1   1   BLUE    1   RED
TESTDECLEF  1   1   1   1   BLUE    2   PINK

But I want to get :

                                1   2
 --------------------------------------
TESTDECLEF  1   1   1   1 BLUE RED  PINK

Thanks for help

标签： sqloraclerow

解决方案

我正在考虑这color_name是 1,2 并且color_value是 PINK，RED。

您可以使用pivot如下：

Select * from
(SELECT  DC.CLEF_NAME,DC.CLEF_X, DC.CLEF_Y, DC.CLEF_COL, DC.CLEF_ROW, DC.CLEF_CLASSCOLOR ,DCO.COLOR_NAME,DCO.COLOR_VALUE
FROM DASHBOARD_SAVE DS 
LEFT JOIN DASHBOARD_CLEF DC ON DC.DASHBOARD_UID = DS.DASHBOARD_UID 
LEFT JOIN DASHBOARD_COLOR DCO ON DCO.CLEF_UID = DC.CLEF_UID
WHERE DC.REGROUPEMENT_UID is null and DS.DASHBOARD_USERID = 'testok')
Pivot
(Max(COLOR_VALUE) for COLOR_NAME IN (1 as 1, 2 as 2)
);

或者你可以使用conditional aggregation如下：

SELECT  DC.CLEF_NAME,DC.CLEF_X, DC.CLEF_Y, DC.CLEF_COL, DC.CLEF_ROW, DC.CLEF_CLASSCOLOR,
Max(case when DCO.COLOR_NAME = 1 then  DCO.COLOR_VALUE end) as 1,
Max(case when DCO.COLOR_NAME = 2 then  DCO.COLOR_VALUE end) as 2
    FROM DASHBOARD_SAVE DS 
    LEFT JOIN DASHBOARD_CLEF DC ON DC.DASHBOARD_UID = DS.DASHBOARD_UID 
    LEFT JOIN DASHBOARD_COLOR DCO ON DCO.CLEF_UID = DC.CLEF_UID
    WHERE DC.REGROUPEMENT_UID is null and DS.DASHBOARD_USERID = 'testok'
Group by 
DC.CLEF_NAME,DC.CLEF_X, DC.CLEF_Y, DC.CLEF_COL, DC.CLEF_ROW, DC.CLEF_CLASSCOLOR;

干杯！！

推荐阅读