DB2 row to column, column change, and other operations

DB2 row to column

----Start

See this question on the Internet: (Problem address:http://www.mydb2.cn/bbs/read.php?tid=1297&page=e& #a)

[C-sharp]View Plaincopyprint?

2. Class Subject Score
4. 1 Languages 8800
6. 1 Mathematics 8420
8. 1 English 7812
10. ......
12. 2 languages 8715
14. 2 Mathematics 8511
16. 2 English 8512
18. ......
24. The request is converted to the following result
26. Class Chinese Math English
28. 1 8800 8420 7812
30. 2 8715 8511 8512

This is a very classic 4 attribute of the table design pattern, as the name implies, such a table generally has four columns, namely:entity_id, attribute_name,Attribute_type, Attribute_ Value, such a design makes it very easy to add fields, such as: we want to add a physical result is very simple, we just insert a record into the table. However, this design has a very serious problem, that is: query difficulty increases, query efficiency is very poor.

One principle to implement the above query is to create a virtual field with a case statement, make the result set a two-dimensional array, and then apply the aggregate function to return a single record. What do you think? Do not understand, take a closer look at the following diagram and analyze the following statement you understand.

[C-sharp]View Plaincopyprint?

2. CREATE TABLE Score
4. (
6. Banji Integer,
8. Kemu varchar (10),
10. Fengshu integer
12. )
14. Go
18. INSERT INTO score values
20. (1, ' language ', 8800),
22. (1, ' math ', 8420),
24. (1, ' English ', 7812),
26. (2, ' language ', 8715),
28. (2, ' math ', 8511),
30. (2, ' English ', 8512)
32. Go
36. Select Banji,
38. Max (Yuwen) languages,
40. Max (Shuxue) Mathematics,
42. Max (Yingyu) English
44. From
46. (Select Banji,
48. Case Kemu
50. When ' Chinese ' then Fengshu
52. Else 0
54. End Yuwen,
56. Case Kemu
58. When ' math ' then Fengshu
60. Else 0
62. End Shuxue,
64. Case Kemu
66. When ' English ' then Fengshu
68. Else 0
70. End Yingyu
72. From score
74. ) as inner
76. GROUP BY Inner.banji
78. ORDER BY 1
80. Go

You may be sigh, such a solution is how ingenious, unfortunately not I think out of, here, I also dare not to take the master's ideas for appropriation, the above ideas from the <sql language Art > 11th chapter, want to know more comprehensive information, we can refer to.

---See more:DB2 SQL Extract

---- statement: Reprint please indicate the source.

----Last Updated on 2009.12.20

----written by Shangbo on 2009.12.16

----End

DB2 Career

Row to Column

Give the following data:
CREATE TABLE Sales (year int, Quarter int, Results int)

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
Desired to the result:
Year Q1 Q2 Q3 Q4
----------- ----------- ----------- ----------- -----------
2004 20 30) 15 10
2005 18 40) 12 27
This SQL solves this problem:
SELECT year,
MAX (case when Quarter = 1
Then Results END) as Q1,
MAX (case when Quarter = 2
Then Results END) as Q2,
MAX (case when Quarter = 3
Then Results END) as Q3,
MAX (case when Quarter = 4
Then Results END) as Q4
From Sales
GROUP by year
Explain why you want to add Max, because without Max, the result would be this:
Year Q1 Q2 Q3 Q4
----------- ----------- ----------- ----------- -----------
2004 20---
2004-30--
2004--15-
2004---10
2005 18---
2005-40--
2005--12-
2005---27

Row career

Give the following data

CREATE TABLE Salesagg
(Year INTEGER,
Q1 INTEGER,
Q2 INTEGER,
Q3 INTEGER,
Q4 INTEGER);

Year Q1 Q2 Q3 Q4
----------- ----------- ----------- ----------- -----------
2004 20 30) 15 10
2005 18 40) 12 27

The desired result
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

This SQL can be implemented as follows:

SELECT s.year, Q.quarter, Q.results
From Salesagg as S,
     TABLE (VALUES (1, s.q1),
                  (2, S.Q2),
                  (3, S.Q3),
                  (4, S.q4))
            As Q (Quarter, Results);

 
table function creates a table, which is a multi-line table with value implementation, and the value implements the virtual table example: 
DB2 = select * FROM (values, (2,3)) as T1 (col1,col2) 

col1 col2
----------------------
 1 2
 2 3

 2 records have been selected. 

DB2 = SELECT * FROM (values 1) as a

1
-----------
1

1 records have been selected.

The difference is that it has a relationship with a table in the FROM clause, and a column in the table is taken as data.

DB2 row to column, column change, and other operations