Use SQL statements to convert data from a strong alignment to a horizontal arrangement (schedule)

Knowledge Focus:

1.extract (Day from Schedule01::timestamp) =13

Extract is a SQL-based DML (that is, the database management language) function, and InterBase also supports Extract, which is used primarily to extract the year, month, day, hour, minute, and second data from a date or time-based field, so it supports its key words, years, months, and days , HOUR, MINUTE, SECOND, WEEKDAY, Yearday.

The syntax for using Extract is:

EXTRACT (key from date or time Type field)

such as: Extract (year from SCHEDULE01) =2017 extracting years from a date

2.max () function: Take maximum value

Nesting of 3.case () functions

Note When you nest the case () function, the start and end of each of the two.

Scheduling function: The existing two people (A and B), they are on different days of duty (state), and now to check their 2017.6 months on duty information

The table structure is as follows:

CREATE TABLE Public.temp_schedule

(

ID integer not NULL DEFAULT nextval (' temp_schedule_id_seq ':: Regclass),

SCHEDULE01 timestamp without time zone,--date

schedule03 character varying (255),--name

State character varying (255),--on duty (0 off 1 classes)

CONSTRAINT Temp_schedule_pkey PRIMARY KEY (ID)

)

1. Query the SQL statement:

Select Schedule03,schedule01,state from Temp_schedule

Where extract (year from SCHEDULE01) =2017 and extract (month from SCHEDULE01) =6

Order BY SCHEDULE03,SCHEDULE01;

Shown as:

2. It is now necessary to arrange the data horizontally from number 1th based on the date of (June) (that is, only two rows are displayed)

The display results are as follows:

The following SQL statements are implemented:

Select SCHEDULE03 as Name

, Max (case if Extract (Day from Schedule01::timestamp) =1 then State end) as Day1

, Max (case if Extract (Day from Schedule01::timestamp) =2 then State end) as Day2

, Max (case if Extract (Day from Schedule01::timestamp) =3 then State end) as Day3

, Max (case if Extract (Day from Schedule01::timestamp) =4 then State end) as Day4

, Max (case if Extract (Day from Schedule01::timestamp) =5 then State end) as Day5

, Max (case if Extract (Day from Schedule01::timestamp) =6 then State end) as Day6

, Max (case if Extract (Day from Schedule01::timestamp) =7 then State end) as Day7

, Max (case if Extract (Day from Schedule01::timestamp) =8 then State end) as Day8

, Max (case if Extract (Day from Schedule01::timestamp) =9 then State end) as Day9

, Max (case if Extract (Day from Schedule01::timestamp) =10 then State end) as Day10

, Max (case if Extract (Day from Schedule01::timestamp) =11 then State end) as Day11

, Max (case if Extract (Day from Schedule01::timestamp) =12 then State end) as Day12

, Max (case if Extract (Day from Schedule01::timestamp) =13 then State end) as Day13

From Temp_schedule

Where extract (year from SCHEDULE01) =2017 and extract (month from SCHEDULE01) =6

Group BY SCHEDULE03;

3. Display the status of the person on duty through kanji (0 off 1 classes), showing the following results:

SQL statements (mainly to implement case nesting):

Select SCHEDULE03 as Name

, Max (case when extract (days from Schedule01::timestamp) =1 then (case is state= ' 0 ' then ' Hugh ' Else ' "end") as Day1

, Max (case when extract (days from Schedule01::timestamp) =2 then (case is state= ' 0 ' then ' Hugh ' Else ' "end") as Day2

, Max (case when extract (days from Schedule01::timestamp) =3 then (case is state= ' 0 ' then ' Hugh ' Else ' "end") as Day3

, Max (case when extract (days from Schedule01::timestamp) =4 then (case is state= ' 0 ' then ' Hugh ' Else ' "end") as Day4

, Max (case when extract (days from Schedule01::timestamp) =5 then (case is state= ' 0 ' then ' Hugh ' Else ' "end") as Day5

, Max (case when extract (days from Schedule01::timestamp) =6 then (case is state= ' 0 ' then ' Hugh ' Else ' "end") as Day6

, Max (case when extract (days from Schedule01::timestamp) =7 then (case is state= ' 0 ' then ' Hugh ' Else ' "end") as Day7

, Max (case when extract (days from Schedule01::timestamp) =8 then (case is state= ' 0 ' then ' Hugh ' Else ' "end") as Day8

, Max (case when extract (days from Schedule01::timestamp) =9 then (case is state= ' 0 ' then ' Hugh ' Else ' "end") as Day9

, Max (case when extract (days from Schedule01::timestamp) =10 then (case is state= ' 0 ' then ' Hugh ' Else ' "end") as Day10

, Max (case when extract (days from Schedule01::timestamp) =11 then (case is state= ' 0 ' then ' Hugh ' Else ' "end") as Day11

, Max (case when extract (days from Schedule01::timestamp) =12 then (case is state= ' 0 ' then ' Hugh ' Else ' "end") as Day12

, Max (case when extract (days from Schedule01::timestamp) =13 then (case is state= ' 0 ' then ' Hugh ' Else ' "end") as Day13

From Temp_schedule

Where extract (year from SCHEDULE01) =2017 and extract (month from SCHEDULE01) =6

Group BY SCHEDULE03;

　Knowledge a little bit of accumulation, technology to improve a little bit! Come on!

Use SQL statements to convert data from a strong alignment to a horizontal arrangement (schedule)