Oracle多栏输出语句

数据表:

ID	NAME
1	苹果汁
2	牛奶
3	蕃茄酱
4	盐
5	麻油
6	酱油
7	海鲜粉
8	胡椒粉
9	鸡
10	蟹
11	民众奶酪
12	德国奶酪
13	龙虾
14	沙茶
15	味精
16	饼干
17	猪肉
18	墨鱼
19	糖果
20	桂花糕
21	花生
22	糯米

欲得到的结果:

ID1	NAME1	ID2	NAME2	ID3	NAME3
1	苹果汁	2	牛奶	3	蕃茄酱
4	盐	5	麻油	6	酱油
7	海鲜粉	8	胡椒粉	9	鸡
10	蟹	11	民众奶酪	12	德国奶酪
13	龙虾	14	沙茶	15	味精
16	饼干	17	猪肉	18	墨鱼
19	糖果	20	桂花糕	21	花生
22	糯米

 使用SQL语句:

WITH  TEST  AS (
SELECT   1   AS  Id,  ' 苹果汁 '   AS  NAME  FROM  Dual  UNION   ALL
SELECT   2   AS  Id,  ' 牛奶 '   AS  NAME  FROM  Dual  UNION   ALL
SELECT   3   AS  Id,  ' 蕃茄酱 '   AS  NAME  FROM  Dual  UNION   ALL
SELECT   4   AS  Id,  ' 盐 '   AS  NAME  FROM  Dual  UNION   ALL
SELECT   5   AS  Id,  ' 麻油 '   AS  NAME  FROM  Dual  UNION   ALL
SELECT   6   AS  Id,  ' 酱油 '   AS  NAME  FROM  Dual  UNION   ALL
SELECT   7   AS  Id,  ' 海鲜粉 '   AS  NAME  FROM  Dual  UNION   ALL
SELECT   8   AS  Id,  ' 胡椒粉 '   AS  NAME  FROM  Dual  UNION   ALL
SELECT   9   AS  Id,  ' 鸡 '   AS  NAME  FROM  Dual  UNION   ALL
SELECT   10   AS  Id,  ' 蟹 '   AS  NAME  FROM  Dual  UNION   ALL
SELECT   11   AS  Id,  ' 民众奶酪 '   AS  NAME  FROM  Dual  UNION   ALL
SELECT   12   AS  Id,  ' 德国奶酪 '   AS  NAME  FROM  Dual  UNION   ALL
SELECT   13   AS  Id,  ' 龙虾 '   AS  NAME  FROM  Dual  UNION   ALL
SELECT   14   AS  Id,  ' 沙茶 '   AS  NAME  FROM  Dual  UNION   ALL
SELECT   15   AS  Id,  ' 味精 '   AS  NAME  FROM  Dual  UNION   ALL
SELECT   16   AS  Id,  ' 饼干 '   AS  NAME  FROM  Dual  UNION   ALL
SELECT   17   AS  Id,  ' 猪肉 '   AS  NAME  FROM  Dual  UNION   ALL
SELECT   18   AS  Id,  ' 墨鱼 '   AS  NAME  FROM  Dual  UNION   ALL
SELECT   19   AS  Id,  ' 糖果 '   AS  NAME  FROM  Dual  UNION   ALL
SELECT   20   AS  Id,  ' 桂花糕 '   AS  NAME  FROM  Dual  UNION   ALL
SELECT   21   AS  Id,  ' 花生 '   AS  NAME  FROM  Dual  UNION   ALL
SELECT   22   AS  Id,  ' 糯米 '   AS  NAME  FROM  Dual
)
SELECT   MAX (Decode(MOD(Id,  3 ),  1 , Id,  '' )) Id1,
                MAX (Decode(MOD(Id,  3 ),  1 , NAME,  '' )) Name1,
                MAX (Decode(MOD(Id,  3 ),  2 , Id,  '' )) Id2,
                MAX (Decode(MOD(Id,  3 ),  2 , NAME,  '' )) Name2,
                MAX (Decode(MOD(Id,  3 ),  0 , Id,  '' )) Id3,
                MAX (Decode(MOD(Id,  3 ),  0 , NAME,  '' )) Name3
FROM  Test
GROUP   BY  Trunc(Rownum  /   3   -   0.1 ,  0 )
ORDER   BY  Id1

 

PS:原贴链接http://www.zlsoft.com/techbbs/dispbbs.asp?boardid=62&id=36640&star=1#346248

     感谢原贴作者