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

Source: Internet
Author: User

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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.