如何将列数据分配到列名称中

Please find below sql query

请在下面找到sql查询

create table test_bin (date1 varchar2(10), desc varchar2(10), values number(10));
insert into test_bin values ('10DEC2015', 'ABC', 10);
insert into test_bin values ('10DEC2015', 'CDE', 20);
insert into test_bin values ('10DEC2015', 'XYZ', 35);
insert into test_bin values ('12DEC2015', 'ABC', 10);
insert into test_bin values ('12DEC2015', 'CDE', 45);
insert into test_bin values ('12DEC2015', 'XYZ', 98);
insert into test_bin values ('13DEC2015', 'ABC', 76);
insert into test_bin values ('13DEC2015', 'CDE', 86);
insert into test_bin values ('13DEC2015', 'XYZ', 63);
commit;

question:- I am expecting following output, I tried my level best i can't able to get proper query

问题： - 我期待以下输出，我尝试了我的水平最好，我无法得到正确的查询

DESC 10DEC2015 12DEC2015 13DEC2015
ABC     10          NULL    NULL
CDE     20          NULL    NULL
XYZ     35          NULL    NULL
ABC     NULL        10      NULL
CDE     NULL        45      NULL
XYZ     NULL        98      NULL
ABC     NULL        NULL     76
CDE     NULL        NULL     86
XYZ     NULL        NULL     63

1 个解决方案

#1

It's just one way :

这只是一种方式：

select desc, 
       SUM(case when date1='10DEC2015' then values end) as "10DEC2015",
       sum(case when date1='12DEC2015' then values end) as "12DEC2015",
       sum(case when date1='13DEC2015' then values end) as "13DEC2015"
from   test_bin
group by date1, desc;

It supposes that you already know all existing values for date1, except if you create dynamically your request.

它假设您已经知道date1的所有现有值，除非您动态创建请求。

PS : be careful about using SQL keyword as column name

PS：注意使用SQL关键字作为列名

PS1 : http://www.sqlfiddle.com/#!9/20068f/5, it's in MySQL because 10g was not available and Oracle is far away from me AND column name has been changed to avoid conflict with SQL keywords

PS1：http：//www.sqlfiddle.com/#！9 / 68f / 5，它在MySQL中，因为10g不可用而且Oracle远离我而且列名已被更改以避免与SQL关键字冲突

#1