在SQL中将列转换为行

I need to write a query which takes rows and converts it into columns - here's my table:

我需要编写一个查询,它接受行并将其转换为列 - 这是我的表:

Count    fname   lname   id
-----------------------------
1        abc     def    20
2        pqr            20      
3        abc     xyz    20  
4        xyz     xyz    20
1        abc     def    21
1        pqr     xyz    22
2        abc     abc    22

This is the output I'm trying to produce:

这是我想要产生的输出:

id  fname  lname  fname  lname  fname  lname  fname  lname
-------------------------------------------------------------
20  abc    def    pqr    NULL   abc    xyz    xyz    xyz
21  abc    def    NULL   NULL   NULL   NULL   NULL   NULL   
22  abc    abc    NULL   NULL   NULL   NULL   NULL   NULL

The max value of count for each id is 4. I'm using Oracle 9i.

每个id的最大计数值为4.我正在使用Oracle 9i。

4 个解决方案

#1

Here's another one you might have some luck with. I like @ThinkJet's but not sure how much decode costs (if more or less than this below.

这是另一个你可能会有运气的人。我喜欢@ ThinkJet,但不确定解码成本是多少(如果比下面更多或更少)。

SELECT
   T1.ID,
   T1.fname,
   T1.lname,
   T2.fname,
   T2.lname,
   T3.fname,
   T3.lname,
   T4.fname,
   T4.lname
FROM
      table T1
   LEFT JOIN
      table T2
   ON
         T1.ID = T2.ID
      AND T2.count = 2
   LEFT JOIN
      table T3
   ON
         T1.ID = T3.ID
      AND T3.count = 3
   LEFT JOIN
      table T4
   ON
         T1.ID = T4.ID
      AND T4.count = 4
WHERE
   T1.count = 1

#2

Look at this example, same principle as in @Mike M. answer, but with true Oracle realization:

看看这个例子,与@Mike M.回答的原理相同,但真正的Oracle实现:

  create table my_table (
    id    number,
    fname varchar2(255),
    lname varchar2(255),
    cnt   number
  );

  insert into my_table(cnt, fname, lname, id) values(1,'abc','def',20);
  insert into my_table(cnt, fname, lname, id) values(2,'pqr',''   ,20);      
  insert into my_table(cnt, fname, lname, id) values(3,'abc','xyz',20);  
  insert into my_table(cnt, fname, lname, id) values(4,'xyz','xyz',20);
  insert into my_table(cnt, fname, lname, id) values(1,'abc','def',21);
  insert into my_table(cnt, fname, lname, id) values(1,'pqr','xyz',22);
  insert into my_table(cnt, fname, lname, id) values(2,'abc','abc',22);

  select 
    tbl.id,
    min(decode(tbl.cnt, 1 , fname, null)) fname_1,
    min(decode(tbl.cnt, 1 , lname, null)) lname_1,
    min(decode(tbl.cnt, 2 , fname, null)) fname_2,
    min(decode(tbl.cnt, 2 , lname, null)) lname_2,
    min(decode(tbl.cnt, 3 , fname, null)) fname_3,
    min(decode(tbl.cnt, 3 , lname, null)) lname_3,
    min(decode(tbl.cnt, 4 , fname, null)) fname_4,
    min(decode(tbl.cnt, 4 , lname, null)) lname_4
  from 
    my_table tbl
  group by 
    tbl.id 
  order by 
    tbl.id  
  ;

#3

I know you're after an Oracle 9i solution, but Oracle 11 introduces PIVOT, which allows you to do queries like:

我知道你是在使用Oracle 9i解决方案,但是Oracle 11引入了PIVOT,它允许你进行如下查询:

select *
  from mb_test
 pivot ( max(fname ) as fname,
         max(lname) as lname
   for count in (1,2,3,4)
       )
order by id
;

which gives:

 ID  1_fname  1_lname  2_fname  2_lname  3_fname  3_lname  4_fname  4_lname
20  abc      def      pqr      null     abc      xyz      xyz      xyz
21  abc      def      null     null     null     null     null     null
22  pqr      xyz      abc      abc      null     null     null     null

Not quite what you were after, but extremely useful in many circumstances.... and almost worth the upgrade for PIVOT and UNPIVOT alone

不完全是你所追求的,但在许多情况下非常有用......并且几乎值得单独升级PIVOT和UNPIVOT

EDIT

Modified to put fname and lname in separate columns

修改为将fname和lname放在单独的列中

#4

-1

Is this what you're looking for?

这是你在找什么?

http://bytes.com/topic/sql-server/answers/531936-convert-rows-into-columns

#1