需要SQL Firebird数据透视表转换

本文介绍了需要SQL Firebird数据透视表转换的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个程序在firebird中生成星期五和星期四的日期.我使用以下查询来生成数据列表:

I have a procedure that generated Friday and Thursday date in firebird. I use the following query to generate a list of data:

select w_start as "Friday", w_end as "Thursday", (select count(*) from course C inner join enrolment E on E.cod_course=C.id where E.date_enrolled between w_start and w_end and C_TYPE = 'PU' and CONFIRMED='C' and (C.name like :PublicCourseOption1 or C.name like :PublicCourseOption2 or C.name like :PublicCourseOption3 or C.name like :PublicCourseOption4 or C.VERSION like :CourseVersion1 or C.version like :CourseVersion2 or C.version like :CourseVersion3 ) ) as "Enrolments", (select list(distinct promotype, ', ') from programmers where datesent between w_start and w_end) as "Promos", (select list(distinct course, ', ') from programmers where datesent between w_start and w_end) as "Courses" from get_weeks(:dtFromDate, :dtToDate) wks order by w_start

它产生以下结果:

Friday Thursday Enrolments Promos Courses 04/01/2013 10/01/2013 5 FAX WHS 11/01/2013 17/01/2013 11 EMAIL WHS 18/01/2013 24/01/2013 6 FAX WHS 25/01/2013 31/01/2013 12 EMAIL, FAX RTW, YSM103 01/02/2013 07/02/2013 17 EMAIL, FAX, Wcover REF-CIT, WHS Toll, WorkCover 08/02/2013 14/02/2013 19 FAX HSR HUR- INFO 15/02/2013 21/02/2013 12 FAX MC 22/02/2013 28/02/2013 19 EMAIL, FAX ARTW, DYS25, MC 01/03/2013 07/03/2013 22 COMCARE, FAX, Wcover COMCARE, COMM, WorkCover 08/03/2013 14/03/2013 13 FAX HSR 15/03/2013 21/03/2013 12 22/03/2013 28/03/2013 16 FAX HSR

有人知道如何将数据转换为显示如下的数据透视表:

Does anyone know how to convert the data into a pivot table showing as follows:

Promos- Course - 10/01/2013 (Thursday Date) - 17/01/2013 (Thursday Date) FAX - WHS - 15 enrolments - 25 enrolments EMAIL - MC - 14 Enrolments - 36 enrolments

日期是动态创建的，并且每次运行查询时都会有所不同，因此静态查询无法满足我的需求.

The dates are dynamically created and can vary every time the query is run, so static query wont work for what I need.

推荐答案

Derek，

在我的工作中，我们遇到了类似的情况.我们的代码是从客户端执行的，因此我们最终要做的是创建一个存储过程，该存储过程返回了SQL语句，然后我们执行了返回的sql语句.

In my line of work we ran into a similar situation. Our code was being executed from a Client so we ended up doing was creating a Stored Procedure that returned the SQL Statement, then we executed the sql Statement returned.

为了完成任务，我创建了一个名为X_GET_ENROLLMENTS_FOR_DATE的Helper存储过程

In order to the accomplish you task, i created a Helper Stored Procedure called X_GET_ENROLLMENTS_FOR_DATE

CREATE OR ALTER PROCEDURE X_GET_ENROLLMENTS_FOR_DATE ( PROMOS VARCHAR(100), COURSES VARCHAR(100), DATE_STRING VARCHAR(30)) RETURNS ( ENROLLMENTS INTEGER) AS BEGIN SELECT SUM(ENROLLMENTS) FROM TABLE_X WHERE TABLE_X.PROMOS = :PROMOS AND TABLE_X.COURSES = :COURSES AND TABLE_X.THURSDAY = :DATE_STRING INTO ENROLLMENTS; SUSPEND; END

然后我使用EXECUTE BLOCK创建了以下内容，但可以轻松地将其放置在另一个存储过程中.

And I created the following using EXECUTE BLOCK, but could easily be placed in another Stored Procedure.

EXECUTE BLOCK RETURNS ( SQL VARCHAR(3000)) AS DECLARE VARIABLE SELECT_SQL VARCHAR(2000); DECLARE VARIABLE WHERE_SQL VARCHAR(2000); DECLARE VARIABLE PROMOS VARCHAR(30); DECLARE VARIABLE COURSES VARCHAR(30); DECLARE VARIABLE THURSDAY VARCHAR(30); DECLARE VARIABLE ENROLLMENTS INTEGER; DECLARE VARIABLE FIELD_ID INTEGER; --DECLARE VARIABLE S varchar(1000) DECLARE VARIABLE FIELD_COUNT INTEGER; BEGIN SELECT_SQL = 'SELECT distinct promos, courses ,' || ASCII_CHAR(13); WHERE_SQL = 'FROM table_x' || ASCII_CHAR(13); FIELD_ID = 0; SELECT COUNT(DISTINCT THURSDAY) FROM TABLE_X INTO :FIELD_COUNT; FOR SELECT DISTINCT THURSDAY FROM TABLE_X INTO :THURSDAY DO BEGIN FIELD_ID = :FIELD_ID + 1; IF (:FIELD_ID = :FIELD_COUNT) THEN SELECT_SQL = :SELECT_SQL || 'T' || :FIELD_ID || '.Enrollments ' || 'Thu_' || REPLACE(:THURSDAY, '/', '_') || ASCII_CHAR(13); ELSE SELECT_SQL = :SELECT_SQL || 'T' || :FIELD_ID || '.Enrollments ' || 'Thu_' || REPLACE(:THURSDAY, '/', '_') || ',' || ASCII_CHAR(13); WHERE_SQL = :WHERE_SQL || 'LEFT OUTER JOIN X_GET_ENROLLMENTS_FOR_DATE ( table_x.PROMOS,table_x.COURSES, ''' || :THURSDAY || ''') T' || :FIELD_ID || ' on (1 = 1)' || ASCII_CHAR(13); -- WHERE_SQL = :WHERE_SQL || 'INNER JOIN ( Select Sum(Enrollments) Enrollments from table_x where promos = ''' || :PROMOS || ''' and Courses = ''' || :COURSES || ''' and thursday = ''' || :THURSDAY || ''') T' || :FIELD_ID || ' on (1 = 1)' || ASCII_CHAR(13); END SQL = :SELECT_SQL || :WHERE_SQL; SUSPEND; END;

执行存储过程时，它将返回以下SQL，然后您可以执行该SQL.

When you execute the Stored procedure it will return back the following SQL, which you can then execute.

SELECT distinct promos, courses , T1.Enrollments Thu_07_02_2013, T2.Enrollments Thu_07_03_2013, T3.Enrollments Thu_10_01_2013, T4.Enrollments Thu_14_02_2013, T5.Enrollments Thu_14_03_2013, T6.Enrollments Thu_17_01_2013, T7.Enrollments Thu_21_02_2013, T8.Enrollments Thu_21_03_2013, T9.Enrollments Thu_24_01_2013, T10.Enrollments Thu_28_02_2013, T11.Enrollments Thu_28_03_2013, T12.Enrollments Thu_31_01_2013 FROM table_x LEFT OUTER JOIN X_GET_ENROLLMENTS_FOR_DATE ( table_x.PROMOS,table_x.COURSES, '07/02/2013') T1 on (1 = 1) LEFT OUTER JOIN X_GET_ENROLLMENTS_FOR_DATE ( table_x.PROMOS,table_x.COURSES, '07/03/2013') T2 on (1 = 1) LEFT OUTER JOIN X_GET_ENROLLMENTS_FOR_DATE ( table_x.PROMOS,table_x.COURSES, '10/01/2013') T3 on (1 = 1) LEFT OUTER JOIN X_GET_ENROLLMENTS_FOR_DATE ( table_x.PROMOS,table_x.COURSES, '14/02/2013') T4 on (1 = 1) LEFT OUTER JOIN X_GET_ENROLLMENTS_FOR_DATE ( table_x.PROMOS,table_x.COURSES, '14/03/2013') T5 on (1 = 1) LEFT OUTER JOIN X_GET_ENROLLMENTS_FOR_DATE ( table_x.PROMOS,table_x.COURSES, '17/01/2013') T6 on (1 = 1) LEFT OUTER JOIN X_GET_ENROLLMENTS_FOR_DATE ( table_x.PROMOS,table_x.COURSES, '21/02/2013') T7 on (1 = 1) LEFT OUTER JOIN X_GET_ENROLLMENTS_FOR_DATE ( table_x.PROMOS,table_x.COURSES, '21/03/2013') T8 on (1 = 1) LEFT OUTER JOIN X_GET_ENROLLMENTS_FOR_DATE ( table_x.PROMOS,table_x.COURSES, '24/01/2013') T9 on (1 = 1) LEFT OUTER JOIN X_GET_ENROLLMENTS_FOR_DATE ( table_x.PROMOS,table_x.COURSES, '28/02/2013') T10 on (1 = 1) LEFT OUTER JOIN X_GET_ENROLLMENTS_FOR_DATE ( table_x.PROMOS,table_x.COURSES, '28/03/2013') T11 on (1 = 1) LEFT OUTER JOIN X_GET_ENROLLMENTS_FOR_DATE ( table_x.PROMOS,table_x.COURSES, '31/01/2013') T12 on (1 = 1)

我想指出的几件事..是我无法使列标题完全符合您的要求.本示例仅适用于星期四"字段，您将需要更改代码以处理星期五"字段.我假设您可能具有相同日期，促销和课程的倍数，所以我将汇总帮助程序存储过程中的注册字段.

I few things i want to point out.. is that i couldn't get the column headers to come exactly as you wanted. This example is only works on Thursday field, you will need to make changes to code to handle the Friday field. I am assuming that you might have multiples of same date, promo and courses so i am summing the enrollments fields in the helper stored procedure.

我希望这会有所帮助，