Oracle multi-row variable one column method

A lot of ways to change a column, I think this first glance understand the use of this method.

This is the case. The first few columns of the following data are the same, and you need to put the values of the Vat_value_char on the same line.

SELECT *
From Ps_vat_defaults defaults
WHERE defaults.vat_driver = ' vat_ent_rgstrn '
and defaults.vat_driver_key1 = ' AMB19 '
and Defaults.vat_driver_key2 = ' DEU '
and Vat_default_type in (' DGS ',
' Eugs ',
' DSP ',
' Eusp ');

SELECT Vat_driver
, Vat_driver_key1
, Vat_driver_key2
, MAX (case if Vat_default_type = ' DGS ' then Vat_value_char END) as VALUE1
, MAX (case is Vat_default_type = ' DSP ' then Vat_value_char END) as VALUE2
, MAX (case is Vat_default_type = ' eugs ' then Vat_value_char END) as VALUE3
, MAX (case is Vat_default_type = ' eusp ' then Vat_value_char END) as VALUE4
From Ps_vat_defaults defaults
WHERE defaults.vat_driver = ' vat_ent_rgstrn '
and Vat_default_type in (' DGS ', ' eugs ', ' DSP ', ' Eusp ')
GROUP by Vat_driver, Vat_driver_key1, Vat_driver_key2

The Wm_concat function is said to be 10g later. He can put all the values of a column of a field in a cell in a comma-delimited form.

SELECT To_char (SUBSTR (Wm_concat (Vat_value_char), 0,80)) Vat_value_char from ps_vat_defaults defaults where Defaults.vat_driver = ' vat_ent_rgstrn ' and defaults.vat_driver_key1= ' AMB19 ' and defaults.vat_driver_key2= ' NLD ' and VAT _default_type in (' DGS ', ' eugs ', ' DSP ', ' Eusp ')

The result is one row (SAL,PURC,ECSL,ECPR

Oracle row and column transfer (from www.askoracle.org finishing)

1. Use case when column career

　　

Select NAME,        max (case when course= ' language ' then  score end) "Language",        Max (case when course= ' math ' then  score end) "Number ",        Max (case when course= ' English ' then  score end ')" English ",        Max (case when course= ' physical ' then  score end)" Physical ",        SUM (Score) "Total score" from Stu GROUP by NAME;

2. Row of data rows to columns

SELECT NAME, case if    LV = 1 then  ' language '--constant when    LV = 2 then  ' math '--constant when    LV = 3 then  ' English ' --constant when    LV = 4 then  ' Physics '--constant   END account, case if    LV = 1 Then langu--column name when    LV = 2 Then math- -Column name when    LV = 3 then english--column name when    LV = 4 then pycial--column name   END score from (  SELECT * from course, (Sele CT level LV from DUAL CONNECT by Level <= 4)  

3. The result set is converted to a row

--Convert the above results to a single line, which can be calculated using SUM or COUNT. SELECT sum (case is DEPTNO = 1 end) d_10,        sum (case is DEPTNO = 1 END) d_20, sum (case when        DEPTN O = 1 END) d_30  from EMP;--You can also use the following method. SELECT case When DEPTNO = Ten then CN end D_10, Case is        DEPTNO = then CN end d_20, case when        DEPTNO = CN END d_30   from (SELECT DEPTNO, COUNT (1) CN from EMP GROUP by DEPTNO);-As you just said, three rows and three columns of data are generated, using MAX to get them. SELECT Max (case when DEPTNO = Ten then CN end) D_10,        max (case-DEPTNO = then CN-end) D_20,        Max (case-When DEP TNO = then CN END) d_30 from   (SELECT DEPTNO, COUNT (1) cn from EMP GROUP by DEPTNO);  

4. Convert the result set to multiple rows

--one column for each job, get the following result set (there is extra NULL in the column for each occupation, and if you use Max, a column will only fetch one maximum value)

SELECT Max (case job "clerk ' then Ename end") Clerk,        Max (case job "analyst" then Ename end) ANALYST,             Max (C When the ASE job is ' manager ' then ename end Manager,        Max (case JOB "President ' then Ename end") president,        Max (case Job when ' salesman ' and Ename END ' salesman   from (SELECT ename,                JOB-                -Each group is sorted starting from 1, and each column has only one set of data. That is, RN is the same in each column there is only one data               row_number () over (PARTITION by JOB ORDER by ename) RN           

Oracle multi-row variable one column method