Transposition algorithm is common for SQL. There are row-to-column transposition, column-to-row transposition, dynamic transposition and join-based transposition, etc. This article analyzes the algorithm for each type of transposition and offers the sample code. For the transpositions that are difficult to handle in SQL, there are convenient esProc solutions. Looking Transpositions in SQL for details.

  A transposition is to rotate information from one row or column to another to change the data layout, for the purpose of making observations from a new perspective. Some transposition algorithms are simple, such as row to column, column to row and bidirectional transposition. Others are not so simple, such as dynamic transposition, transposition with inter-row calculations and join-based transposition. All are commonly seen in data analyses and thus worth a study.

Basic transposition

  Row-to-column transposition and column-to-row transposition are the simplest. Each is the other’s inverse computation.

  1. Row to column: Below is the grouped sales table. Task: Transpose values (rows) Q1-Q4 under quarter field into new field names (columns), as shown below:

year	quarter	amount		---->	year	Q1	Q2	Q3	Q4
year2018	Q1	89			year2018	89	93	88	99
year2018	Q2	93			year2019	92	97	90	88
year2018	Q3	88
year2018	Q4	99
year2019	Q1	92
year2019	Q2	97			，
year2019	Q3	90
year2019	Q4	88

  2. Column to row: Below is the sales cross table. Task: Transpose fields Q1-Q4 to values Q1-Q4 under the new field quarter, as shown below:

Year	Q1	Q2	Q3	Q4		---->	year	quarter	amount
year2018	89	93	88	99			year2018	Q1	89
year2019	92	97	90	88			year2018	Q2	93
							year2018	Q3	88
							year2018	Q4	99
							year2019	Q1	92
							year2019	Q2	97
							year2019	Q3	90
							year2019	Q4	88

 

The early SQL solutions:

  In its earlier days, SQL didn’t have the special PIVOT function (MySQL and HSQLDB don’t have one even now), it handled row to column transpositions by using and coordinating multiple basic functions. There was often more than one way to solve a computing problem.

  Method 1: case when subquery + grouping & aggregation

/*mysql*/

Select year, max(Q1) 'Q1',  max(Q2)   'Q2',  max (Q3) 'Q3', max (Q4) 'Q4'

 from   (

                select year,

                case when   quarter = 'Q1' then amount end Q1,

                case when   quarter = 'Q2' then amount end Q2,

                case when   quarter = 'Q3' then amount end Q3,

                case when   quarter = 'Q4' then amount end Q4

                from zz11

) t  group by year;

 

  Method 2: sum if + grouping & aggregation

| 
/\*mysql\*/

SELECT year,

    MAX(IF(quarter = 'Q1',   amount, null)) AS 'Q1',

    MAX (IF(quarter = 'Q2',   amount, null)) AS 'Q2',

    MAX (IF(quarter = 'Q3',   amount, null)) AS 'Q3',

    MAX (IF(quarter = 'Q4', amount, null)) AS   'Q4'

 FROM zz11 GROUP BY year;

 |

  Other methods include WITH ROLLUP + grouping & aggregation and UNION + grouping & aggregation, etc. They are essentially the same: calculate the year value after grouping, and generate new columns Q1-Q4 through enumeration and their values through aggregation.

  The SQL code is lengthy even for the most basic and simplest transposition. This is because you need to enumerate each new column. The more the new columns there are, the longer the code will be. Imagine the code if the new columns are 12 months, states and provinces in a country.

  Only if the new columns are known, the inefficient enumeration of the new columns will affect the length of the code only but not the complexity of the code. But if the new columns cannot be known in advance, it’s difficult to enumerate them. One example is to convert the dynamic row-based VIP customers into field names. It’s hard to do this in SQL alone. Usually we turn to the stored procedure or high-level language, like Java, to handle it. But the code complexity and maintenance cost will considerably increase.

  There’s another problem about the above SQL program. That’s the hard to understand aggregate algorithm. There is only one record in each quarter per year, and there’s no need to do the aggregation. But as SQL forces an aggregation after each grouping, there’s unreasoned aggregate over the single record after grouping for the calculation of the year column. It’s unnecessary and senseless. You can do any aggregate and get same result, to replace MAX with SUM, for instance.

  SQL’s binding of aggregate to each grouping action results from its incomplete set-orientation. Specifically, SQL can only express a simple, small set consisting of multiple records, but it doesn’t have the syntax or operator to phrase a complicated, large set made up of multiple smaller sets. That’s why it can’t help aggregating each subgroup to convert it a single record to make a large set of smaller sets a simple set.

  The column to row transposition doesn’t involve the hard to understand aggregation. Its early SQL solution is relatively simple. You just need to get records under Q1-Q4 by column names and then union them. The code is as follows:

select year, 'Q1' quarter , Q1 as amount from zz111

union

select year, 'Q2' quarter , Q2 as amount from zz111

union

select year, 'Q3' quarter , Q3 as amount from zz111

union

select year, 'Q4' quarter , Q4 as amount from zz111

  Though it is simple, the code is very long because you need to enumerate new rows in each group, which could be a quarter, a month or a state. Fortunately the new rows in each group are the column (field) names of the source table and they are fixed rather than dynamic. So the algorithm is not complicated.

PIVOT/UNPIVOT functions

  To make the transposition convenient, database vendors released special functions to implement the algorithms.

  The PIVOT function for performing row to column transposition:

/*oracle*/

select * from zz111

              pivot(

                        max(amount)   for quarter in(

                                       'Q1'as Q1,'Q2' as Q2,'Q3' as   Q3,'Q4' as Q4

                        )

              )

  PIVOT function shortens the code, but doesn’t hit the real problem. SQL’s weaknesses are still there.

  It can’t handle the problem of dynamic columns. The stored procedure or Java is still needed. And the code is difficult to develop and maintain.

  It can’t deal with the problem of set-based operations. All it can do is the aggregation for all scenarios, even unnecessary. For beginning users, that’s the hard nut to crack and needs a lot of extra efforts.

  In certain cases, the aggregation is necessary. To do the row to column transposition to get multiple rows for the record of each quarter per year, and calculate the biggest amount per quarter per year based on the grouped sales table, for example:

customer	year	quarter	amount			year	Q1	Q2	Q3	Q4
companyA	year2018	Q1	89		---->	year2018	89	93	88	100
companyB	year2018	Q1	100			year2019	92	97	90	88
companyA	year2018	Q2	93
companyB	year2018	Q3	88
companyC	year2018	Q4	99
companyD	year2019	Q1	92
companyE	year2019	Q2	97
companyF	year2019	Q3	90
companyG	year2019	Q4	88

  Aggregation is reasonable and necessary in such a case, so we can use the same core code:

/*oracle*/

select * from (select year,quarter,amount from zz111)

              pivot(

                         max(amount)   for quarter in(

                                    'Q1'as Q1,'Q2' as Q2,'Q3' as   Q3,'Q4' as Q4

                           )

              )

  Now you can see that this is the “grouping & aggregation + row-to-column transposition” instead of a pure transposition. Beginners may wonder why we use same core code for two different algorithms. If you have read the previous part carefully, you know that’s due to SQL’s incomplete set orientation.

  UNPIVOT function is easier to understand:

select * from zz111

               unpivot(

                       amount for   quarter in(

                                Q1,Q2,Q3,Q4

                       )

                )

  UNPIVOT produces shorter and easy to understand code. The code is simple because there isn’t aggregate operation involved. Besides, it’s rare that a column to row transposition involves dynamic column names. So the algorithm won’t be too complicated. In view of these, UNPIVOT is perfect.

Bidirectional transposition

  A bidirectional transposition is the swapping or mapping of rows or columns to another over, generally, a crosstab.

  3. Task: To transpose Year-Quarter sales table to Quarter-Year sales table. That is to convert the Year values year2018 and year 2019 to new column names and at the same time, to transform column names Q1-Q4 to values of the new column quarter.

  The expected result is as follows:

Year	Q1	Q2	Q3	Q4	---->	quarter	year2018	year2019
year2018	89	93	88	99		Q1	89	92
year2019	92	97	90	88		Q2	93	97
						Q3	88	90
						Q4	99	88

  As the name shows, the bidirectional transposition is to first perform a column to row over Q1-Q4 and then a row to column over year2018 and year2019. The code will be like this if you do it with a small database:

/*mysql*/

select quarter,

    max(IF(year = 'year2018',   amount, null)) AS 'year2018',

    max(IF(year = 'year2019',   amount, null)) AS 'year2019'

from (

        select year, 'Q1'   quarter , Q1 as amount from crosstb

        union

        select year, 'Q2'   quarter , Q2 as amount from crosstb

        union

        select year, 'Q3'   quarter , Q3 as amount from crosstb

        union

        select year, 'Q4'   quarter , Q4 as amount from crosstb

) t

group by quarter

  As there are both the row to column and column to row algorithms in the program, it has the weaknesses of both, such as lengthy code, dynamic column problem and unintelligible aggregate operation. A procedural language, like Java and C++, follows, in order, a set of commands, so the relationship between code complexity and code length is linear. There’s a different case with SQL. It’s hard to write a SQL program in a step-by-step or module-by-module way or debug one through breakpoint. This leads to an exponential increase of code complexity as the code becomes longer. All these make the bidirectional transposition more difficult to implement than it appears.

  It appears that you can reverse the order to perform row to column and then column to row. Actually it won’t work because it will increase the number of subqueries from 1 to 4 as the result of union. That will further produce longer code and lower performance. But there will be no such problems if you use databases that support WITH clause, like Oracle.

  You can make a duo of PIVOT and UNPIVOT if you use Oracle or MSSQL instead of a small database that requires WITH clause. The code is as follows:

/*Oracle*/

select * from(

        select * from crosstb unpivot(

                amount for   quarter in(

                          Q1,Q2,Q3,Q4

                )

        )

) pivot(

        max(amount) for year   in(

                 'year2018' as   year2018,'year2019' as year2019

        )

) order by quarter

  he order of column to row result is random, so you need to use order by to sort quarter column according to Q1-Q4. If you want to arrange it by a user-defined order (like 0,a,1), then you need to create a pseudo table and perform join with it. This will greatly complicate the computation.

  Another point about PIVOT/UNPIVOT functions is that they are not ANSI standard. Vendors have their own ways to implement them and so it’s difficult to migrate their code between different databases.

 

Dynamic transposition

  A dynamic transposition has unfixed, changeable to-be-transposed values and thus the indefinite transposed rows or columns that require dynamic calculations.

  4. Dynamic row to column transposition: There’s a Dept-Area average salary table where the number of areas increases as businesses expand. Task: Convert values under Area field (rows) to new field names (columns).

  As shown in the figure below：

Dept	Area	AvgSalary	---->	Dept	Beijing	Shanghai	...
Sales	Beijing	3100		Sales	3100	2700
Marketing	Beijing	3300		Marketing	3300	2400
HR	Beijing	3200		HR	3200	2900
Sales	Shanghai	2700
Marketing	Shanghai	2400
HR	Shanghai	2900
	…

  It seems that we can get this done using PIVOT with a subquery used in the in clause to dynamically get the unique area values, as shown below:

/*Oracle 11*/

select * from temp pivot (

    max(AvgSalary) for Area in(

          select distinct Area   from temp

    )

)

  Actually PIVOT’s in function is different by that it doesn’t support a direct subquery.

  To use a subquery directly, you need the unusual xml key word:

/*Oracle 11*/

select * from temp pivot xml(

    max(AvgSalary) for Area in(

          select distinct Area   from temp

    )

)

  And get a strange intermediate result set consisting of two fields, one of which is XML type, as shown below:

Dept	Area_XML
HR	<PivotSet><item><column   name =  "AREA">Beijing</column><column   name ="MAX(AVGSALARY)">3200</column></item><item><column  name ="AREA">Shanghai</column><column   name =   "MAX(AVGSALARY)">3200</column></item></PivotSet>
Marketing	<PivotSet><item><column   name =  "AREA">Beijing</column><column   name ="MAX(AVGSALARY)">3300</column></item><item><column    name ="AREA">Shanghai</column><column   name = "MAX(AVGSALARY)">2400</column></item></PivotSet>
Sales	<PivotSet><item><column   name =  "AREA">Beijing</column><column   name ="MAX(AVGSALARY)">3100</column></item><item><column    name ="AREA">Shanghai</column><column   name = "MAX(AVGSALARY)">2700</column></item></PivotSet>

  Then you need to parse the XML dynamically to get the AREA nodes and generate a dynamic table structure to which data will be populated dynamically. It’s impossible to implement such a dynamic algorithm in SQL alone. For subsequent computation, you need to embed the SQL code in Java or stored procedure. The code will become extremely long.

  5. Row to column transposition over intra-group records: In the income source table, Name is the logical grouping field; Source and Income is the intra-group fields. Each Name corresponds to multiple records in the group, whose number is indefinite. Task: transpose rows to columns for each group.

  Below is the source data and the expected transposed data:

Name	Source	Income	---->	Category	Source1	Income1	Source2	Income2
David	Salary	8000		David	Salary	8000	Bonus	15000
David	Bonus	15000		Daniel	Salary	9000
Daniel	Salary	9000		Andrew	Shares	26000	Sales	23000
Andrew	Shares	26000		Robert	Bonus	13000
Andrew	Sales	23000
Robert	Bonus	13000

  The logic is clear: generate the result table structure, insert data into it and then export data from it.

  Yet the implementation is not simple at all. The code needs a lot of dynamic syntax, even in the nested loop, but SQL doesn’t support the dynamic syntax. To make up for it, SQL turns to another language to do the job, like Java or the stored procedure, which are not good at handling structured computations. Forcing them to do this will result in lengthy code. Below is the SQL solution:

  1. Calculate the number of intra-group fields (colN) in the result table. To do this, group the source table by Name, get the number of records in each group, and find the largest record count. In the above table, both David and Andrew have two records, which is the most. So colN is 2, and the dynamic column name is colNames.

  2. Dynamically generate the SQL strings (cStr) for the result table。This requires looping over records for colN times to generate a set of intra-group fields each time. The fields include one fixed column and 2*colN dynamic columns (as the above table shows).

  3. Execute the generated SQL strings dynamically to generate a temporary table using the code like execute immediate cStr.

  4. Get the list of key words (rowKeys) to be inserted into the result table, which is performing distinct over the source table. The key word list for the above table is rowKeys=["David","Daniel","Andrew","Robert"].

  5. Loop over rowKeys to dynamically generate a SQL string iStr to be inserted into the result table and then execute the insertion. To generate iStr, query the source table by the current Name to get the corresponding list of records. Both the generation of iStr and the subsequence execution are dynamic. Then loop over the list of records to compose an iStr to execute. That’s the end of one round of loop.

  6. Query the result table to return the data.

  The code would be much simpler if SQL supported dynamic syntax or Java/the stored procedure has built-in structured function library (which is independent of SQL).

  The algorithm of step 4 is removing duplicates from Name values, which is equivalent to getting values of the grouping filed after data is grouped. That of step 1 is to count the records in each group after the grouping operation. Since both have the grouping action, the grouping result can be reused in theory. But as an aggregate will always follow a grouping action due to SQL’s incomplete set orientation, reuse of grouping result is disabled. When there is only a small amount of data, reuse is not that important if you don’t care about whether the code is graceful or not. But, when there is a large amount of data or the algorithm requires frequent reuses, reusability will affect the performance.

  6. Complex static row-to-column transposition: There will be a fixed 7 records for each person per day on the attendance table. Now we want to transpose each set of records into 2 records. Values of In, Out, Break, Return fields in the first record come from the Time values in the 1^st, 7^th, 2^nd and 3^rd records in the source table. Values of the second record correspond to the Time values of the 1^st, 7^th, 5^th and the 6^th records.

  The source table:

Per_Code	in_out	Date	Time	Type
1110263	1	2013-10-11	09:17:14	In
1110263	6	2013-10-11	11:37:00	Break
1110263	5	2013-10-11	11:38:21	Return
1110263	0	2013-10-11	11:43:21	NULL
1110263	6	2013-10-11	13:21:30	Break
1110263	5	2013-10-11	14:25:58	Return
1110263	2	2013-10-11	18:28:55	Out

  The expected transposed table:

Per_Code	Date	In	Out	Break	Return
1110263	2013-10-11	09:17:14	18:28:55	11:37:00	11:38:21
1110263	2013-10-11	09:17:14	18:28:55	13:21:30	14:25:58

  Since the number of columns after transposition is fixed, we can use SQL to implement the algorithm. The code is as follows:

With r as(

  select Per_code,Date,Time,row_number()  over(partition by Per_Code,Date order by Time) rn from temp)

select Per_code,Date,

max(case when rn=1   then Time end) In,

max(case when rn=7   then Time end) Out,

max(case when rn=2   then Time end) Break,

max(case when rn=3   then Time end) Return

from r group by   Per_code,Date

union

select Per_code,Date,

max(case when rn=1   then Time end) In,

max(case when rn=7   then Time end) Out,

max(case when rn=5   then Time end) Break,

max(case when rn=6   then Time end) Return

from r group by   Per_code,Date

  SQL is based on unordered sets. It doesn’t support referencing records directly with sequence numbers. To make the data retrieval convenient, we have to create sequence numbers manually using the with clause. As we explained earlier, the additional aggregate max is the display of SQL’s incomplete set orientation.

  7. Complex dynamic row-to-column transposition: The user table relates the record table through user IDs. Each user has an activity record in a certain date of the year 2018. Task: Find whether each user has the activity record in each week of 2018. User names will be transposed to new columns.

  The source table structure:

User		Record
ID(pk)	1:N---->	ID(pk)
Name		Date(pk)

  The expected transposed table:

Week	User1	User2	User3
1	Yes	No	Yes
2	Yes	Yes	No
3	Yes	No	Yes
4	No	Yes	Yes

  We need to implement the dynamic columns using the stored procedure/Java + dynamic SQL. The code will be very long.

  We need some preparations. Join the user table and the record table; add a calculated column and calculate which week the current Date field value falls beginning from 2018-01-01 (the result should be not greater than 53); find the maximum number of weeks to get the key word list rowKeys for the target table; perform distinct over the join result and get the new column names colNames for the target table.

  Then we begin to implement the dynamic transposition algorithm: generate a dynamic SQL query according to colNames to create the target table and execute the query; loop through rowKeys to first get data from the join result and then generate Insert SQL dynamically and then execute the dynamic SQL.

  All transpositions involving dynamic columns include generating the dynamic target table structure and then inserting data dynamically. The implementation is difficult and we have to turn to Java or the stored procedure because SQL lacks the ability to express dynamic query. I’ll simply use the “dynamic transposition” for similar scenarios in my later illustrations.

Transposition + inter-column calculation

  The pure transposition exists only in exercise book most of the time. In real-world businesses, a transposition is often accompanied by another, or others, operations, such as the inter-column calculation.

  8 Temp table stores the monthly payable amount for each customer in 2014. The name filed is the key (key words). Now we want to transpose the months in dates into the new columns (Month 1-12). Their corresponding values are the monthly payable amount. If the amount is null for a month, just use the amount of the previous month.

  The source table:

ID	Name	amount_payable	due_date
112101	CA	12800	2014-02-21
112102	CA	3500	2014-06-15
112104	LA	25000	2014-01-12
112105	LA	20000	2014-11-15
112106	LA	8000	2014-12-06

  The target transposed table:

name	1	2	3	4	5	6	7	8	9	10	11	12
CA		12800	12800	12800	12800	3500	3500	3500	3500	3500	3500	3500
LA	25000	25000	25000	25000	25000	25000	25000	25000	25000	25000	20000	8000

  We can handle the transposition in SQL since the columns are fixed after transposition. The algorithm is like this. Create a temporary table t1 made up of one field month whose values are 1-12; get the month according to the dates in the source table and name the field month; perform a left join between the two tables to create continuous payable amount records that include invalid data; use PIVOT to do the row to column transposition and remove invalid data through min aggregate. The SQL code is as follows:

With t2 as(select   name,amount_payable,EXTRACT(MONTH from dule_date) month from temp

)

,t1 as(SELECT rownum   month FROM dual CONNECT BY LEVEL <= 12

)

,t3 as(select   t2.name,t2.amount_payable,t1.month from t1 left join t2 on   t1.month>=t2.month

)

 select * from t3  pivot(min(amount_payable) for month in(1 as   "1",2 as "2",3 as "3",4 as "4",5 as   "5",6 as "6",7 as "7",8 as "8",9 as   "9",10 as "10",11 as "11",12 as   "12"))

  The code is not very long but difficult to understand, particularly with the extra creation of invalid data. That’s because SQL sets don’t have sequence number and the language isn’t good at performing order-based calculations, especially the inter-row calculation.

Table join + column to row transposition

  9 Insert sub table into the main table: The relationship of Order table and OrderDetail table is that of the sub table and the main table. One order corresponds to at least one detail record. We want to insert the details into the orders, as shown below:

  The relationship between the source tables:

Order	---->	OrderDetail
ID(pk)		OrderID(PK)
Customer		Number(pk)
Date		Product
		Amount

  The target transposed table:

ID	Customer	Date	Product1	Amount1	Product2	Amount2	Product3	Amount3
1	3	2019-01-01	Apple	5	Milk	3	Salt	1
2	5	2019-01-02	Beef	2	Pork	4
3	2	2019-01-02	Pizza	3

  We use the stored procedure/Java + dynamic SQL to implement the dynamic columns. The algorithm is this. Join the two tables; group the joining result (or the sub table) by ID, count records in each group, and find the largest number of records to get the value of colNames, the dynamic columns list; perform distinct over the joining result (or the main table) by ID to get the key word list rowKeys for the target table; implement the dynamic transposition algorithm according to colNames and rowKeys.

  10 Table join + column to row transposition: Both Exam table and Retest table are Students table’s sub tables. We want to convert the data in the sub tables into the main table’s new columns and add a total_score column. The exam subjects may vary for different students and not every student participates in the retest. The exam subjects always include the retest subject(s).

  The source tables and their relationship:

Exam   table			<----1:N	Students   table			1:N ---->	Retest   table
stu_id	subject	score			stu_id	subject	score			stu_id
stu_id	stu_name	class_id			stu_id	stu_name	class_id			stu_id
stu_id	subject	score			stu_id	subject	score			stu_id
1	Chinese	80			1	Chinese	80			1
1	Ashley	301			1	Ashley	301			1
2	Chinese	78			2	Chinese	78			2
1	Math	77			1	Math	77			1

  The target transposed table:

stu_id	stu_name	Chinese_score	Math_score	total_score	Chinese_retest	Math_retest
1	Ashley	80	77	156
2	Rachel	58	67	125	78
3	Emily	85	56	141		82

  If the exam subjects are fixed, we can do it in SQL. Left join Students table and Exam table and perform PIVOT; Left join Retest table and Exam table and perform PIVOT; and then perform another left join between the two result tables.

  But in this case the subjects are not fixed and so the target table will have dynamic columns. The old trick again, which is the stored procedure/Java + dynamic SQL. The algorithm is like this. Left join both sub tables to Students table; group the joining result by stu_id, count records in each group and find the largest record count to get the dynamic columns list (colNames) for the target table; perform distinct over the joining result by stu_id and get the key word list (rowKeys) for the target table; implement the dynamic transposition algorithm by colNames and rowKeys.

Display data in column groups

  11 The source table records populations in certain cities in different continents. We want to get European and African cities and their populations and display them in two column groups horizontally. The target columns are fixed but the number of rows in the source table is dynamic:

Continent	City	Population	---->	EuropeCity	EuropePopulation	AfricaCity	EuropePopulation
Africa	Cairo	6789479		Moscow	8389200	Cairo	6789479
Africa	Kinshasa	5064000		London	7285000	Kinshasa	5064000
Africa	Alexandria	3328196				Alexandria	3328196
Europe	Moscow	8389200
Europe	London	7285000

  We can implement a target table with a fixed structure in SQL. The algorithm is this. Filter records to get those of European cities and calculate row numbers through rownum to make them a calculated column; get records of African cities in the same way; perform full join between them and get the desirable fields.

  The SQL code:

With t1 as(select city   Europecity,population Europepopulation,rownum rn from temp where   continent='Europe')

,t2 as(select city   Africacity,population Africapopulation,rownum rn from temp where   continent='Africa')

select   t1.Europecity,t1.Europepopulation,t2.Africacity,t2.Africapopulation from t1   full join t2 on t1.rn=t2.rn

Summary

  After the detailed explanations, you can see that there are only 3 types of simple transpositions that can be directly handled using SQL PIVOT and UNPIVOT in large databases. And you need to take care of XML parsing, unordered result set and migration problem.

  For transposition algorithms that are not very simple, SQL can handle them if columns are fixed but the code is difficult to write. You need to be familiar with SQL weaknesses and devise ingenious and unusual techniques to make up for them. The SQL defects include incomplete set orientation, lack of sequence numbers for elements in a set, order-based calculation headache, nonprocedural calculation and difficult to debug code, etc.

  For algorithms involving dynamic columns, the coe will be difficult to write and you have to turn to Java or stored procedure to produce very complicated code. The lack of support of dynamic data structure is another SQL flaw.

  SQL headaches owe to limitation of times when it was born. This doesn’t exist in other computer languages, like VB\C++\JAVA, and the stored procedure. But on the other hand, these languages have weaker set-based computing ability, lack class library for structured computations and thus need to write a lot of code to implement the transposition algorithms if no SQL program is embedded into them.

  Yet all these problems can be solved with esProc SQL. esProc is the professional data computing engine that is based on ordered sets, provides all-round structured computation functions as SQL does and intrinsically supports stepwise coding and execution as Java does. It inherits the merits of both SQL and Java. You can always use SPL instead of Java with SQL to handle the transposition tasks effortlessly:

  1 Row to column transposition using PIVOT-like function

	A
1	=connect("orcl").query@x("select   * from T")
2	=A1.pivot(year; quarter, amount)

  2 Column to row transposition using UNPIVOT-like function

	A
1	=connect("orcl").query@x("select   year,Q1,Q2,Q3,Q4 from T")
2	=A1.pivot@r(year; quarter, amount)

  3 Bidirectional transposition using PIVOT and its inversed operation

	A
1	=connect("orcl").query@x("select   year,Q1,Q2,Q3,Q4 from T")
2	=A1.pivot@r(year;quarter,amount).pivot(quarter;year,amount)

  4 Dynamic row to column transposition; SPL’s PIVOT supports dynamic data structure

	A
1	=connect("orcl").query@x("select   Dept,Area,AvgSalary  from T")
2	=A1.pivot@r(year;quarter,amount).pivot(Dept;   Area, AvgSalary)

  5 Column to row transposition over intra-group records; SPL support step-by-step computation and dynamic data structure

	A	B
1	=orcl.query("select Name, Source,   Income from T")
2	=gData=A1.group(Name)
3	=colN=gData.max(~.len())
4	=create(Name,${colN.("Source"+string(~)+",   Income"+string(~)).concat@c()})
5	for gData	=A5. Name | A5.conj([Source,  Income])
6		>A4.record(B5)

  6 Complex, static row to column/column to row transposition; SPL supports sequence numbers

	A	B
1	=connect("orcl").query@x("select * from DailyTime    order by Per_Code, Date, Time")	=A1.group((#
2	=create(Per_Code,Date,In,Out,Break,Return)	=B1.(~([1,7,2,3,1,7,5,6]))
3	=B2.conj([~.Per_Code,~.Date]|~.(Time).m([1,2,3,4])|[~.Per_Code,~.Date]|~.(Time).m([5,6,7,8]))	>A2.record(A3)

  7 Complex, dynamic row to column/column to row transposition

	A	B
1	=connect("db").query("select    t1.ID as ID, t1.Name as Name, t2.Date   as Date from User t1, Record t2 where  t1.ID=t2.ID")
2	=A1.derive(interval@w("2018-01-01",Date)+1:Week)	=A2.max(Week)
3	=A2.group(ID)	=B2.new(~:Week,${A3.("\"No\":"+Name).concat@c()})
4	=A3.run(~.run(B3(Week).field(A3.#+1,"Yes")))

  8 Transposition + inter-column calculation

	A	B
1	=orcl.query@x("select   name,amount_payable from T")
2	=create(name,${12.string@d()})	=A1.group(customID)
3	for B2	=12.(null)
4		>A3.run(B3(month(due_date))=  amount_payable)
5		>B3.run(~=ifn(~,~[-1]))
6		=A2.record(B2.name|B3)

  9 Insert sub table into the main table dynamically

	A	B
1	=orcl.query@x("select * from OrderDetail   left join Order on  Order.ID=OrderDetail.OrderID")
2	=A1.group(ID)	=A2.max(~.count()).("Product"+string(~)+","+"Amount"+string(~)).concat@c()
3	=create(ID,Customer,Date,${B2})	>A2.run(A3.record([ID,Customer,Date]|~.([Product,Amount]).conj()))

  10 Table join + column to row transposition

	A	B
1	=orcl.query@x("select   t1.stu_id stu_id,t1.stu_name stu_name,t2.subject  subject,t2.score score1,t3.score score2   from Students  t1 left join Exam t2 on t1.stu_id=t2.stu_id  left join Retest t3 on   t1.stu_id=t3.stu_id and  t2.subject=t3.subject order by   t1.stu_id,t2.subject
2	=A1.group(stu_id)	=A1.group(subject)
3	=create(stu_id,stu_name,${(B2.(~.subject+"_score")|"total_score"|B2.(~.subject+"_retest  ")).string()})
4	>A2.run(A3.record([stu_id,stu_name]|B2.(~(A2.#).score1)|A2.sum(score1)|B2.(~(A2.#).score2)))

  11 Display data in column groups

	A	B
1	=orcl.query@x("select * from   World where Continent  in('Europe','Africa')")
2	=A1.select(Continent:"Europe")	=A1.select(Continent:"Africa")
3	=create('Europe   City',Population,'Africa City', Population)	=A3.paste(A2.(City),A2.(Population),B2.(City),B2.(Population))