在SQL中将列转换为行 [英] Convert columns to rows in 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

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

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

推荐答案

这是您可能还有些运气的另一种.我喜欢@ThinkJet的解码器，但不确定多少解码成本(如果大于或小于此值).

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.

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

这篇关于在SQL中将列转换为行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！