Shulou(Shulou.com)05/31 Report--

It is believed that many inexperienced people have no idea about what the row-column transposition of DB2 is like. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Create a table and insert 2 rows of data

CREATE TABLE SalesAgg (year INTEGER, Q1 INTEGER, Q2 INTEGER, Q3 INTEGER, Q4 INTEGER)

Insert into SalesAgg values (2004, 20, 30, 15, 10)

Insert into SalesAgg values (2005, 18, 40, 12, 27)

Select * from SalesAgg

YEAR Q1 Q2 Q3 Q4

-

2004 20 30 15 10

2005 18 40 12 27

Want to convert the data to the following look

YEAR QUARTER RESULTS

-

2004 1 20

2004 2 30

2004 3 15

2004 4 10

2005 1 18

2005 2 40

2005 3 12

2005 4 27

SQL that converts columns to rows

SELECT Year, Quarter, Results

FROM SalesAgg AS S

LATERAL (VALUES (1, S.q1))

(2, S.q2)

(3, S.q3)

(4, S.q4))

AS Q (Quarter, Results); LATERAL can be replaced by TABLE, the result is the same, the purpose is to combine values into a temporary table Q used in SQL

SELECT Year, Quarter, Results

FROM SalesAgg AS S

TABLE (VALUES (1, S.q1))

(2, S.q2)

(3, S.q3)

(4, S.q4))

AS Q (Quarter, Results)

Another example of using TABLE

SELECT id ANSWER

, salary AS sal

, comm AS com

, combo

, typ

FROM staff

, TABLE (

VALUES (

Salary

, 'SAL'

)

, (

Comm

, 'COM'

)

) AS tab (combo, typ)

WHERE id < 40

ORDER BY id

, typ

Example of row-to-column use

CREATE OR REPLACE PROCEDURE DB2INST1.TEST (OUT out_msg VARCHAR (4000))

SPECIFIC DB2INST1.TEST

MODIFIES SQL DATA

NOT DETERMINISTIC

NULL CALL

LANGUAGE SQL EXTERNAL ACTION

INHERIT SPECIAL REGISTERS

BEGIN

DECLARE v_col1s VARCHAR (4000)

DECLARE v_sql VARCHAR (4000)

SELECT SUBSTR (XMLCAST (XMLGROUP (','| | col1 AS an ORDER BY col1) as varchar (4000)), 2) into v_col1s FROM aaa

SET out_msg = 'SELECT * FROM aaa where col1 in (' | | TRIM (v_col1s) | |')'

After reading the above, have you mastered the method of row transposition of DB2 rows and columns? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.