比如已有数据结构如下name | Item
a 1
a 2
a 3
a 4
b 1
b 3
b 4
c 2
使用select * from table 查询的化就如上了,
如何使用SQL语句,使查询后数据结构如下:
name| 1 | 2 | 3 | 4
a 1 1 1 1
b 1 0 1 1
c 0 1 0 0SQL数据库查询
a 1
a 2
a 3
a 4
b 1
b 3
b 4
c 2
使用select * from table 查询的化就如上了,
如何使用SQL语句,使查询后数据结构如下:
name| 1 | 2 | 3 | 4
a 1 1 1 1
b 1 0 1 1
c 0 1 0 0SQL数据库查询
解决方案 »
- help !! help 这个题
- 请教一个SQL语句~~
- oracle导出卡壳
- 关于FORALL 批量提交问题
- asp调用oralce数据库存储过程出错!!!大家来看看吧。
- 看下sql怎么写
- 给点建表的建议。在线等待......
- beckhambobo(beckham) 请进
- 怎么把sys的v_$session的select权限授予其他用户?
- 請問,開發pro*c/c++的,用什麽編輯器比較好?
- UPDATE tablename SET s="locked" WHERE s="unlocked" AND id=x;能有行锁的效果么?
- RMB 200元 odac 连接oracle 9i 汉字字段名乱码
WITH T AS(
SELECT 'A' AS NAME,1 AS ITEM FROM DUAL UNION ALL
SELECT 'A' AS NAME,2 AS ITEM FROM DUAL UNION ALL
SELECT 'A' AS NAME,3 AS ITEM FROM DUAL UNION ALL
SELECT 'A' AS NAME,4 AS ITEM FROM DUAL UNION ALL
SELECT 'B' AS NAME,1 AS ITEM FROM DUAL UNION ALL
SELECT 'B' AS NAME,3 AS ITEM FROM DUAL UNION ALL
SELECT 'B' AS NAME,4 AS ITEM FROM DUAL UNION ALL
SELECT 'C' AS NAME,2 AS ITEM FROM DUAL
)
SELECT * FROM T;
--使用DECODE拆分行
WITH T AS(
SELECT 'A' AS NAME,1 AS ITEM FROM DUAL UNION ALL
SELECT 'A' AS NAME,2 AS ITEM FROM DUAL UNION ALL
SELECT 'A' AS NAME,3 AS ITEM FROM DUAL UNION ALL
SELECT 'A' AS NAME,4 AS ITEM FROM DUAL UNION ALL
SELECT 'B' AS NAME,1 AS ITEM FROM DUAL UNION ALL
SELECT 'B' AS NAME,3 AS ITEM FROM DUAL UNION ALL
SELECT 'B' AS NAME,4 AS ITEM FROM DUAL UNION ALL
SELECT 'C' AS NAME,2 AS ITEM FROM DUAL
)
SELECT NAME,DECODE(ITEM,1,1,0) AS ITEM1,
DECODE(ITEM,2,1,0) AS ITEM2,
DECODE(ITEM,3,1,0) AS ITEM3,
DECODE(ITEM,4,1,0) AS ITEM4
FROM T;
--使用聚集函数,获取值
WITH T AS(
SELECT 'A' AS NAME,1 AS ITEM FROM DUAL UNION ALL
SELECT 'A' AS NAME,2 AS ITEM FROM DUAL UNION ALL
SELECT 'A' AS NAME,3 AS ITEM FROM DUAL UNION ALL
SELECT 'A' AS NAME,4 AS ITEM FROM DUAL UNION ALL
SELECT 'B' AS NAME,1 AS ITEM FROM DUAL UNION ALL
SELECT 'B' AS NAME,3 AS ITEM FROM DUAL UNION ALL
SELECT 'B' AS NAME,4 AS ITEM FROM DUAL UNION ALL
SELECT 'C' AS NAME,2 AS ITEM FROM DUAL
)
SELECT NAME,
SUM(DECODE(ITEM,1,1,0)) AS ITEM1,
SUM(DECODE(ITEM,2,1,0)) AS ITEM2,
SUM(DECODE(ITEM,3,1,0)) AS ITEM3,
SUM(DECODE(ITEM,4,1,0)) AS ITEM4
FROM T
GROUP BY NAME;
结果如下:
NAME ITEM1 ITEM2 ITEM3 ITEM4
A 1 1 1 1
B 1 0 1 1
C 0 1 0 0
这个是制式的了,反正只要是"行转列"的问题,都可以用这个思路去解决,这个问题的搜索关键字"行转列"
PIVOT
(
COUNT(*)
FOR ITEM IN ('1','2','3','4')
)
1.这个 要在ORACLE 11G 以上才能用
2.要明确知道具体的值,如 ('1','2','3','4')
有个有类型的效果的select TA.NAME, wmsys.wm_concat(TA.ITEM) ITEM
from TA
group by TA.NAME