如何让一个字段(竖列)内的数据，横向显示。
比如已有数据结构如下
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 0 SQL 数据库 查询
[解决办法]
--测试数据
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;
