SQL：将行转换为列 [英] SQL : Turning rows into columns

问题描述

我需要将行的值转换为列-例如：

SELECT s.section_name, 
       s.section_value 
  FROM tbl_sections s

此输出：

section_name   section_value
-----------------------------
sectionI       One
sectionII      Two
sectionIII     Three

所需输出：

sectionI      sectionII      sectionIII
-----------------------------------------
One           Two            Three

推荐答案

这可能更好地在客户端使用您选择的编程语言完成。

您绝对需要提前知道节名才能将它们转换为列名。

Oracle 11g的更新答案(使用new PIVOT operator)：

SELECT * FROM 
  (SELECT section_name, section_value FROM tbl_sections)
PIVOT
  MAX(section_value) 
    FOR (section_name) IN ('sectionI', 'sectionII', 'sectionIII')

对于旧版本，您可以执行一些自联接：

WITH
  SELECT section_name, section_value FROM tbl_sections
AS 
  data
SELECT
   one.section_value 'sectionI', 
   two.section_value 'sectionII', 
   three.section_value 'sectionIII'
FROM 
   select selection_value from data where section_name = 'sectionI' one
  CROSS JOIN
   select selection_value from data where section_name = 'sectionII' two
  CROSS JOIN
   select selection_value from data where section_name = 'sectionIII' three

或同时使用MAX技巧和"Aggregate"：

SELECT 
   MAX(DECODE(section_name, 'sectionI', section_value, '')) 'sectionI',
   MAX(DECODE(section_name, 'sectionII', section_value, '')) 'sectionII',
   MAX(DECODE(section_name, 'sectionIII', section_value, '')) 'sectionIII'
FROM tbl_sections

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