An example of the row-to-column and column-to-Row Operations in Oracle Data Tables.

An example of the row-to-column and column-to-Row Operations in Oracle Data Tables.

Row-to-Column
One table

The query result is

-- Row-to-column Conversion

select years,(select amount from Tb_Amount as A where month=1 and A.years=Tb_Amount.years)as m1,(select amount from Tb_Amount as A where month=2 and A.years=Tb_Amount.years)as m2,(select amount from Tb_Amount as A where month=3 and A.years=Tb_Amount.years)as m3from Tb_Amount group by years

Or

Select years as year, sum (case when month = '1' then amount end) as January, sum (case when month = '2' then amount end) as February, sum (case when month = '3' then amount end) as March from dbo. tb_Amount group by years order by years desc

2. The personnel information table includes the name time amount

Show row-to-Column
Name time amount

Name: Young, middle-aged, and elderly

Zhang Li 1000000.00 4000000.00 500000000.00

Sun Tzu 2000000.00 12233335.00 4552220010.00

Select uname as name, SUM (case when era = 'Young 'then amount end) as young, SUM (case when era = 'middle-aged' then amount end) as middle-aged, SUM (case when era = 'Elder 'then amount end) as elder from Tb_People group by uname order by uname desc

3. Student table [Tb_Student]

Display Effect

Static SQL indicates that subject has only three courses: Chinese, mathematics, and English.

Select sname as name, max (case Subject when 'chine' then grade else 0 end) as language, max (case Subject when 'mate' then grade else 0 end) as mathematics, max (case Subject when 'English 'then grade else 0 end) as English from dbo. tb_Student group by sname order by sname desc

-- Dynamic SQL refers to subject more than three courses: Chinese, mathematics, and English.

Declare @ SQL varchar (8000) set @ SQL = 'select sname as '+ 'name' select @ SQL = @ SQL + ', max (case Subject when ''' + Subject + ''' then grade else 0 end) ['+ Subject +'] 'from (select distinct Subject from Tb_Student) as aset @ SQL = @ SQL + 'from Tb_Student group by sname order by sname desc' exec (@ SQL)

Use the Decode () function in oracle and then sum the sum (sum)

Select t. sname AS name, sum (decode (t. subject, 'China', grade, null) language, sum (decode (t. subject, 'mat', grade, null) mathematics, sum (decode (t. subject, 'English ', grade, null) English from Tb_Student t group by sname order by sname desc

Column-to-row

Generate

SQL code
Generate static:

Select * from (select sname, [Course] = 'mate', [Score] = [math] from Tb_students union allselect sname, [Course] = 'English ', [Score] = [English] from Tb_students union allselect sname, [Course] = 'China', [Score] = [language] from Tb_students) torder by sname, case [Course] when 'chine' then 1 when' math 'then 2 when' then 3 endgo -- Static scheme of column-to-row: unsung, sql2005 and later versions SELECT sname, subject, grade from dbo. tb_students unregister (grade for Subject in ([Chinese], [mathematics], [English]) as up GO -- Dynamic Solution for column-to-row: unsung, sql2005 and later versions -- because the rows are dynamic, we can obtain columns from the INFORMATION_SCHEMA.COLUMNS view to construct rows. XML processing is also used. Declare @ s nvarchar (4000) select @ s = isnull (@ s + ',', '') + quotename (Name) from syscolumns where ID = object_id ('tb _ students ') and Name not in ('sname') order by Colidexec ('select sname, [Subject], [grade] from Tb_students unregister ([grade] for [Subject] in ('+ @ s +') B ') goselect sname, [Subject], [grade] from tb_studentsun.pdf ([grade] for [Subject] in ([mathematics], [English], [Chinese]) B

Articles you may be interested in:

• Detailed use example of to_date in oracle (oracle date format conversion)
• Common ORACLE numeric functions, conversion functions, and string functions