oracle 总共有200个不同的设施代码,但My one limit_id最多可以有16个设施代码,因此,需要混凝土16个新柱,如何转置?[关闭]

ylamdve6  于 2023-06-22  发布在  Oracle
关注(0)|答案(1)|浏览(102)

8天前关闭
Improve this question
我使用的是Oracle SQL数据库。
尝试在sql中将行转置为16个新列。
总共有200个不同的设施代码,但My one limit_id最多可以有16个设施代码。因此,需要创建16个新列。
我正在尝试使用sql case when语句,但由于220个不同的facilty_code而无法实现结果,并且需要在16列中容纳。
SQL代码:

SELECT 
  lim_id, 
  lim_ver_num, 
  max(
    case when lim_id = lim_id 
    and lim_ver_num = lim_ver_num then 'facility_code' else '' end
  ) as facility_code1, 
  max(
    case when lim_id = lim_id 
    and lim_ver_num = lim_ver_num then 'facility_code' else '' end
  ) as facility_code2, 
  max(
    case when lim_id = lim_id 
    and lim_ver_num = lim_ver_num then 'facility_code' else '' end
  ) as facility_code3, 
  max(
    case when lim_id = lim_id 
    and lim_ver_num = lim_ver_num then 'facility_code' else '' end
  ) as facility_code4, 
  max(
    case when lim_id = lim_id 
    and lim_ver_num = lim_ver_num then 'facility_code' else '' end
  ) as facility_code5, 
  max(
    case when lim_id = lim_id 
    and lim_ver_num = lim_ver_num then 'facility_code' else '' end
  ) as facility_code6, 
  max(
    case when lim_id = lim_id 
    and lim_ver_num = lim_ver_num then 'facility_code' else '' end
  ) as facility_code7, 
  max(
    case when lim_id = lim_id 
    and lim_ver_num = lim_ver_num then 'facility_code' else '' end
  ) as facility_code8, 
  max(
    case when lim_id = lim_id 
    and lim_ver_num = lim_ver_num then 'facility_code' else '' end
  ) as facility_code9, 
  max(
    case when lim_id = lim_id 
    and lim_ver_num = lim_ver_num then 'facility_code' else '' end
  ) as facility_code10, 
  max(
    case when lim_id = lim_id 
    and lim_ver_num = lim_ver_num then 'facility_code' else '' end
  ) as facility_code11, 
  max(
    case when lim_id = lim_id 
    and lim_ver_num = lim_ver_num then 'facility_code' else '' end
  ) as facility_code12, 
  max(
    case when lim_id = lim_id 
    and lim_ver_num = lim_ver_num then 'facility_code' else '' end
  ) as facility_code13, 
  max(
    case when lim_id = lim_id 
    and lim_ver_num = lim_ver_num then 'facility_code' else '' end
  ) as facility_code14, 
  max(
    case when lim_id = lim_id 
    and lim_ver_num = lim_ver_num then 'facility_code' else '' end
  ) as facility_code15, 
  max(
    case when lim_id = lim_id 
    and lim_ver_num = lim_ver_num then 'facility_code' else '' end
  ) as facility_code16 
FROM 
GROUP BY 
  lim_id, 
  lim_ver_num;
  • -当前表格格式如下
    Limit_ID Version_numer Facility_code 12345 1 AA 12345 1 BB 12345 1 CC 12345 1 DD 12345 1 EE
    12345 1 FF 12345 1 GG 12345 1 HH 12345 1 II 12345 1 JJ 12345 1 KK 12345 1 LL 12345 1 MM 12345 1 NN 12345 1 OO 12345 1 PP 6789 2 RR 6789 2 SS 6789 2 TT 6789 2 UU 6789 2 VV 6789 2 WW 6789 2 XX
  • -我想使用oracle sql转换为以下格式,Limit_ID Version_numer code1 code2 code3 code4 code5 code6 code7 code8 code9 code10 code11 code12 code13 code14 code15 code16
    12345 1 AA BB CC DD EE FF GG HH II JJ KK LL MM NN OO PP
    6789 2 RR SS TT UU VV WW XX
yi0zb3m4

yi0zb3m41#

看起来您可以在LIMIT_ID(和/或VERSION_NUMBER)中创建行号并使用它进行透视。

WITH            --  S a m p l e   D a t a :
    tbl (LIMIT_ID, VERSION_NUMBER, FACILITY_CODE) AS
        (   
            Select 12345, 1, 'AA' From Dual Union All       Select 12345, 1, 'BB' From Dual Union All   
            Select 12345, 1, 'CC' From Dual Union All       Select 12345, 1, 'DD' From Dual Union All
            Select 12345, 1, 'EE' From Dual Union All       Select 12345, 1, 'FF' From Dual Union All
            Select 12345, 1, 'GG' From Dual Union All       Select 12345, 1, 'HH' From Dual Union All
            Select 12345, 1, 'II' From Dual Union All       Select 12345, 1, 'JJ' From Dual Union All
            Select 12345, 1, 'KK' From Dual Union All       Select 12345, 1, 'LL' From Dual Union All
            Select 12345, 1, 'MM' From Dual Union All       Select 12345, 1, 'NN' From Dual Union All
            Select 12345, 1, 'OO' From Dual Union All       Select 12345, 1, 'PP' From Dual Union All
            Select  6789, 2, 'QQ' From Dual Union All       Select  6789, 2, 'RR' From Dual Union All
            Select  6789, 2, 'SS' From Dual Union All       Select  6789, 2, 'TT' From Dual Union All
            Select  6789, 2, 'UU' From Dual Union All       Select  6789, 2, 'VV' From Dual Union All
            Select  6789, 2, 'WW' From Dual Union All       Select  6789, 2, 'XX' From Dual Union All
            Select  6789, 2, 'YY' From Dual Union All       Select  6789, 2, 'ZZ' From Dual 
        )
--  M a i n   S Q L :
SELECT      *
FROM        (   Select    LIMIT_ID, VERSION_NUMBER, FACILITY_CODE,
                          ROW_NUMBER() OVER(Partition By LIMIT_ID, VERSION_NUMBER 
                                            Order By FACILITY_CODE) "RN"
                From      tbl
            )
            PIVOT ( Max(FACILITY_CODE)  FOR RN IN(  1  "CODE_1",  2  "CODE_2",  3  "CODE_3",  4  "CODE_4", 
                                                    5  "CODE_5",  6  "CODE_6",  7  "CODE_7",  8  "CODE_8", 
                                                    9  "CODE_9", 10 "CODE_10", 11 "CODE_11", 12 "CODE_12", 
                                                   13 "CODE_13", 14 "CODE_14", 15 "CODE_15", 16 "CODE_16")
                )
ORDER BY LIMIT_ID DESC, VERSION_NUMBER

回复:
| 限值ID|版本号|代码_1| CODE_2| CODE_3|代码_4|代码_5|代码_6|代码_7|代码_8|代码_9|代码_10|代码_11|代码_12|代码_13|代码_14|代码_15|代码_16|
| - -----|- -----|- -----|- -----|- -----|- -----|- -----|- -----|- -----|- -----|- -----|- -----|- -----|- -----|- -----|- -----|- -----|- -----|
| 一二三四五|1| AA| BB| CC| DD| EE| FF| GG| HH|二|JJ| KK| LL| MM| NN| OO| PP|
| 六七八九|2| QQ| RR| SS| TT| UU|验证与确认|WW| XX| YY| ZZ|零|零|零|零|零|零|

相关问题