8-SQL row-to-column Conversion

Reading directory

I. Preface

Ii. First Thought

Iii. Second Thought

4. The third thought

I. Preface

In some of our projects, we often encounter the problem of converting rows into columns. Now let's discuss how we can convert Table 1 to table 2 format.

Figure 1

Figure 2

Ii. First Thought

As you can see, table 2 columns are converted from the data rows in table 1, and only the name column is the column in table 1. It is difficult to implement other columns step by step, if Table 2 has only one column of name, it is simple, not a simple group.

Select [name] From nameandsubjectandgrade group by [name]

Now the SQL statement architecture is built up. grouping is indispensable no matter how it changes in the future.

Ii. Second Thought

Now we want to add one more column in this result set. We do not add more columns because you can process Chinese, mathematics, and English columns, if other columns are copied as they are, add a Chinese column to the current column.

Select [name], case when subject = 'China' then grade end from nameandsubjectandgrade group by [name]

We can see the error message that the subject and grade columns must be in the aggregate function or the group by clause, so we should first place the subject and grade columns in the group by clause.

Select [name], case when subject = 'China' then grade end from nameandsubjectandgrade group by [name], subject, grade

There is data, but there are more rows. It seems that we can only start with aggregation.

III. The third thought

Starting with aggregate Functions

Select [name],

Sum (case when subject = 'China' then grade else 0 end) as Yuwen

From nameandsubjectandgrade group by [name]

The other columns are easy to handle. We use our CTR + C, CTR + V

Select [name],

Sum (case when subject = 'China' then grade end) as Yuwen,

Sum (case when subject = 'mate' then grade end) as shuxue,

Sum (case when subject = 'English 'Then grade end) as yingyu

From nameandsubjectandgrade group by [name]

Iv. Fourth thought

Mat6 skipped class all day and didn't take the test in mathematics and English. So we should not show 'null', it should be '0', but the result is null, which is not very good, let's write case when then end, and add an else 0 between them.

Select [name],

Sum (case when subject = 'China' then grade else 0 end) as Yuwen,

Sum (case when subject = 'mate' then grade else 0 end) as shuxue,

Sum (case when subject = 'English 'Then grade else 0 end) as yingyu

From nameandsubjectandgrade group by [name]

Finished