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

This article will explain in detail how to achieve the row transfer Pivot function in SQL. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

Let's create a DailyIncome table first.

Create table DailyIncome (VendorId nvarchar (10), IncomeDay nvarchar (10), IncomeAmount int)-VendorId supplier ID,--IncomeDay revenue time-IncomeAmount revenue amount

Then let's insert the data and take a look.

(take a look, some suppliers will have multiple revenues in a day, which should be paid in batches.)

Insert into DailyIncome values ('SPIKE',' FRI',) insert into DailyIncome values ('SPIKE',' MON',) insert into DailyIncome values ('FREDS',' SUN',) insert into DailyIncome values ('SPIKE',' WED', 500) insert into DailyIncome values ('SPIKE',' TUE',) insert into DailyIncome values ('JOHNS',' WED',) insert into DailyIncome values ('SPIKE',' FRI', 100) insert into DailyIncome values ('JOHNS',' MON' Insert into DailyIncome values ('SPIKE',' SUN',) insert into DailyIncome values ('JOHNS',' FRI', 300) insert into DailyIncome values ('FREDS',' TUE', 500) insert into DailyIncome values ('FREDS',' TUE', 200) insert into DailyIncome values ('SPIKE',' MON', 900) insert into DailyIncome values ('FREDS',' FRI', 900) insert into DailyIncome values ('FREDS',' MON', 500) insert into DailyIncome values ('JOHNS',' SUN' Insert into DailyIncome values ('SPIKE',' FRI',) insert into DailyIncome values ('SPIKE',' WED', 500) insert into DailyIncome values ('SPIKE',' FRI', 300) insert into DailyIncome values ('JOHNS',' THU', 800) insert into DailyIncome values ('JOHNS',' SAT',) insert into DailyIncome values ('SPIKE',' TUE', 100) insert into DailyIncome values ('SPIKE',' THU', 300) insert into DailyIncome values ('FREDS',' WED' Insert into DailyIncome values ('SPIKE',' SAT',) insert into DailyIncome values ('FREDS',' SAT', 500) insert into DailyIncome values ('FREDS',' THU', 800) insert into DailyIncome values ('JOHNS',' TUE', 600)

Let's first look at the first ten rows of data:

Select top 10 * from DailyIncome

As shown in the figure:

DailyIncome

Although the data can be fully displayed, it seems that we can't get the information that is more useful to us at a glance, for example, we want to get the total income of each supplier every day, then we should make some changes in the form of data. this is what you usually use.

Select VendorId, sum (case when IncomeDay='MoN' then IncomeAmount else 0 end) MON,sum (case when IncomeDay='TUE' then IncomeAmount else 0 end) TUE,sum (case when IncomeDay='WED' then IncomeAmount else 0 end) WED,sum (case when IncomeDay='THU' then IncomeAmount else 0 end) THU,sum (case when IncomeDay='FRI' then IncomeAmount else 0 end) FRI,sum (case when IncomeDay='SAT' then IncomeAmount else 0 end) SAT,sum (case when IncomeDay='SUN' then IncomeAmount else 0 end) SUNfrom DailyIncome group by VendorId

The results are as follows:

Case when result

If you look closely at the results, you will find that VendorID is grouped, and the value in the column IncomeDay in each group is changed to a new column name, and then the IncomeAmount is summed.

It may be a bit troublesome to write in this way. don't worry, let's try the row-to-row column with the Pivot function.

Select * from DailyIncome-first step pivot (sum (IncomeAmount)-third step for IncomeDay in ([MON], [TUE], [WED], [THU], [FRI], [SAT], [SUN]) as AvgIncomePerDay

To explain, if you want to make good use of the Pivot function, you should understand these steps in the code comments.

Step 1: be sure to understand the data source. This is DailyIncome.

Step 2: understand which column value you want to make the new column name

Step 3: understand what values are required for this new column?

Here is an exercise question. Don't look at the answer before you do it.

Q: for SPIKE, the supplier, the maximum amount recorded per day.

Select * from DailyIncomepivot (max (IncomeAmount) for IncomeDay in ([MON], [TUE], [WED], [THU], [FRI], [SAT], [SUN]) as MaxIncomePerDaywhere VendorId in ('SPIKE') this is the end of the article on "how to implement row transfer Pivot function in SQL". I hope the above content can be helpful to you, so that you can learn more knowledge, if you think the article is good. Please share it for more people to see.

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.