将列值转换为行值

i have a table like below

我有一张如下表

   structure_no  |   element_name
        1        |      BASIC
        1        |      HRA
        2        |      BASIC
        2        |      HRA

I would like to get the values

我想得到价值观

   structure_no    |  element1   | element2
        1          |  BASIC      |   HRA 
        2          |  BASIC      |   HRA

Kindly help me in forming SQL Query

请帮我形成SQL查询

2 个解决方案

#1

SOLUTION APPROACH 1

解决方案1

Using Oracle 11G's pivot option, you can do as below:

使用Oracle 11G的数据透视选项,您可以执行以下操作:

SELECT *
   FROM   MY_DATA_TABLE
  PIVOT (MIN(ELEMENT_NAME)
   FOR    ELEMENT_NAME  IN ('BASIC' AS ELEMENT1,'HRA' AS ELEMENT2) );

Refer Fiddle Here : http://sqlfiddle.com/#!4/7f4cd/2

请参阅Fiddle Here:http://sqlfiddle.com/#!4/7f4cd/2

SOLUTION APPROACH 2:

解决方案2:

You can also use decode and aggregate function with group by as below:

您还可以使用解码和聚合功能与组,如下所示:

SELECT STRUCTURE_NO,
MAX(DECODE (ELEMENT_NAME,'BASIC',ELEMENT_NAME)) ELEMENT1,
MAX(DECODE (ELEMENT_NAME,'HRA',ELEMENT_NAME)) ELEMENT2
FROM SAMPLE_TABLE
GROUP BY STRUCTURE_NO;

Refer Fiddle Here : http://sqlfiddle.com/#!4/7f4cd/4

请参考这里的小提琴:http://sqlfiddle.com/#!4/7f4cd/4

#2

I don't think this can be done. There is no way to have a set number of fields. You can get a count of all records with that structure_no and you can select all records with that structure_no but I do not believe what you are trying to do can be done with a query.

我不认为这可以做到。没有办法拥有一定数量的字段。您可以使用该structure_no获取所有记录的计数,并且您可以选择具有该structure_no的所有记录,但我不相信您尝试执行的操作可以通过查询完成。

Perhaps reconsider your table structure and the use of relationships.

也许重新考虑你的表结构和关系的使用。

#1

SOLUTION APPROACH 1