时间戳

首页 > 解决方案 > 需要从行更改为列

sql - 需要从行更改为列

问题描述

我有一张像

id,Jdate,Desc
1,3-jul,a
2,3-jul,b
3,3-jul,c
1,4-jul,a
2,4-jul,b

询问：

SELECT jdate,id_1,desc_1,id_2,desc_2,id_3,desc_3
from
(select jdate,id,
'id'+ CAST (row_number() OVER(PARTIOTION BY jdate order by jdate) AS varchar(5))AS id_seq,
desc,
'desc' + CAST (row_number() OVER(PARTIOTION BY jdate order by jdate) AS varchar(5))AS id_seq
FROM temp)temp
PIVOT
(MAX(lotid)
FOR id_seq IN(id_1,id_2,id_3)
)PIV;

我需要输出为

jdate,id_1,desc_1,id_2,desc_2,id_3,desc_3
3-jul,1,a,2,b,3,c
4-jul,1,a,2,b

标签： sqloraclepivot

解决方案

获得所需输出的另一种方法是通过以下查询

with abc as 
(SELECT jdate, LISTAGG(id||','||desc, ',') WITHIN GROUP (ORDER BY id) AS list_1 
FROM   test_table
GROUP BY jdate) 
select abc.jdate, regexp_substr(list_1,'[^,]+',1,1)  id_1 ,
                  regexp_substr(list_1,'[^,]+',1,2) desc_1, 
                  regexp_substr(list_1,'[^,]+',1,3) id_2,
                  regexp_substr(list_1,'[^,]+',1,4) desc_2,
                  regexp_substr(list_1,'[^,]+',1,5) id_3,
                  regexp_substr(list_1,'[^,]+',1,6) desc_3
from abc

或者

如果您只寻找数据输出，那么您可以在下面使用

    select     'Jdate'||','||'id_1'||','||'desc_1'||','||'id_2'||','||'desc_2'||','||'id_3'||','|| 'desc_3'  DATA from dual
    union
    SELECT jdate||','|| LISTAGG(id||','||desc, ',') WITHIN GROUP (ORDER BY id) as DATA FROM   test_table GROUP BY jdate
   order by data desc

希望这会有所帮助。

推荐阅读