An example of a row and column change in an Oracle datasheet explain _oracle

Row to Column
a table

Query results are

--row column

Select years, (select amount from Tb_amount as A where Month=1 and A.years=tb_amount.years) as M1,
(select amount from T B_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 M3 from
tb_amount GROUP by years

or for

Select years as year,
sum (case when month= ' 1 ' then Amount ") as January,
 sum (case when month= ' 2 ' then amount) as February ,
sum (case when month= ' 3 "then amount end) as the March from
dbo. Tb_amount GROUP BY years order by years Desc

2. The Personnel information table includes the name age amount

Show row-Column
Name Times Amount

Name Young middle-aged old

Zhang 1000000.00 4000000.00 500000000.00

Grandson 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 age ' then amount end) as middle-aged, C3/>sum (case when era= ' old age ' then amount end) as old from
tb_people Group by uname ORDER BY uname Desc

3. Student table [Tb_student]

Display effects

Static SQL, refers to the subject only language, mathematics, English these three courses.

Select Sname as name,
Max (case Subject when ' language ' then grade else 0 end) as language,
Max (case Subject when ' math ' 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 the language, mathematics, English these three courses.

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 a
se T @sql = @sql + ' from Tb_student Group by sname sname Desc '
exec (@sql)

The decode () function in Oracle uses and then sums these totals (sum part)

Select T.sname as name,
sum (decode (t.subject, ' language ', grade,null)) language,
sum (decode (t.subject, ' mathematics ', Grade,null)) Mathematics,
sum (decode (t.subject, ' English ', grade,null)) English to
tb_student T Group by sname ORDER BY sname Desc

Column Careers

Generated

SQL code
Generate Static:

SELECT * FROM
(select sname,[course] = ' math ', [score]=[Mathematics] from tb_students UNION ALL
Select sname,[course]= ' English ', [ score]=[English] from tb_students UNION ALL
Select sname,[course]= ' language ', [score]=[language] from tb_students) T
Sname,case [Course] when ' language ' then 1 when ' math ' then 2 when ' English ' then 3 end
Go
 --column career change static scheme: unpivot,sql2005 and later versions
 
 SELECT Sname,subject, grade from
 dbo. Tb_students
 Unpivot (grade for Subject in ([Language],[Mathematics],[English]) as up go
 
 
 --List of dynamic schemes for career change: unpivot,sql2005 and later versions
 -Because the line is dynamic, so here's the information_schema. The columns view gets columns to construct rows, and 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
Colid
exec (' select Sname,[subject],[grade] from Tb _students Unpivot ([grade] for [Subject] in (' +@s+ ')) B ' "

go
select
  Sname,[subject],[grade]
  tb_students
unpivot
  ([grade] for [Subject] in ([Math],[English],[language]) b