需要SQL Firebird数据透视表转换 [英] SQL Firebird Pivot table conversion required
问题描述
我有一个程序在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.
我希望这会有所帮助，
这篇关于需要SQL Firebird数据透视表转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
