Shulou(Shulou.com)06/01 Report--

Create an Employee table with two columns, one is name and the other is the department to which you belong (dept)

CREATE TABLE Employee (name VARCHAR (15), dept VARCHAR (15))

Insert into Employee values ('Jack','L3')

Insert into Employee values ('Lily','Quality')

Insert into Employee values ('Mark','ID')

Insert into Employee values ('Lee','L3')

Insert into Employee values ('Serge','Solutions')

Insert into Employee values ('John','Development')

Insert into Employee values ('Miso','Solutions')

Insert into Employee values ('Berni','Solutions')

Select * from Employee

NAME DEPT

--

Jack L3

Lily Quality

Mark ID

Lee L3

Serge Solutions

John Development

Miso Solutions

Berni Solutions

Now I want to write a SQL to count the employees of a department, one line for each department.

The data looks like the following

DEPT NAMES

--

Development John

ID Mark

L3 Jack,Lee

Quality Lily

Solutions Berni,Miso,Serge

Realized SQL

SELECT Dept

, SUBSTR (Names, 1, LENGTH (names)-1)

FROM (

SELECT Dept

, REPLACE (REPLACE (XMLSERIALIZE (CONTENT XMLAGG (XMLELEMENT (NAME a, NAME) ORDER BY NAME) AS VARCHAR (60)),','),',') AS Names

FROM Employee

GROUP BY Dept

) AS X; explain the meaning of several DB2 XML methods

XMLELEMENT is a format that converts scalars into XML.

Select Dept,XMLELEMENT (NAME a, NAME) from Employee

DEPT 2

--

L3 Jack

Quality Lily

ID Mark

L3 Lee

Solutions Serge

Development John

Solutions Miso

Solutions Berni

XMLAGG aggregates multiple XML. Here, the grouped column (Dept) is given, and each group is sorted by NAME.

Select Dept,XMLAGG (XMLELEMENT NAME a, NAME) ORDER BY NAME) from Employee GROUP BY Dept

DEPT 2

Development John

ID Mark

L3 Jack Lee

Quality Lily

Solutions Berni Miso Serge

The purpose of XMLSERIALIZE () is to convert XML to a String type

Select Dept,XMLSERIALIZE (CONTENT XMLAGG (XMLELEMENT (NAME a, NAME) ORDER BY NAME) AS VARCHAR (60) from Employee GROUP BY Dept

DEPT 2

Development John

ID Mark

L3 Jack Lee

Quality Lily

Solutions Berni Miso Serge

When you get to this place, it's very simple. Just kill it and turn it into,

Later, XMLGROUP appeared, and it is also easy to use SELECT Dept.

, XMLGROUP (','| | NAME AS an ORDER BY NAME)

FROM Employee

GROUP BY Dept

DEPT 2

--

Development, John

ID, Mark

L3, Jack, Lee

Quality, Lily

Solutions, Berni, Miso, Serge

SELECT Dept

, XMLCAST (XMLGROUP (','| | NAME AS an ORDER BY NAME) AS VARCHAR (60))

FROM Employee

GROUP BY Dept

DEPT 2

--

Development, John

ID, Mark

L3, Jack,Lee

Quality, Lily

Solutions, Berni,Miso,Serge

SELECT Dept

, SUBSTR (XMLCAST (XMLGROUP (','| | NAME AS an ORDER BY NAME) AS VARCHAR (60)), 2) AS Names

FROM Employee

GROUP BY Dept

DEPT NAMES

--

Development John

ID Mark

L3 Jack,Lee

Quality Lily

Solutions Berni,Miso,Serge

After DB2 9.7.4, this problem has been solved perfectly.

SELECT Dept

LISTAGG (name,',')

WITHIN GROUP (ORDER BY name)

FROM Employee

GROUP BY Dept

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.