SQL Server column Career pivot use

Source: Internet
Author: User

Today is just the data show, using the column change, row to column there are many ways, pivot is one of them, Povit is the function of SQL Server 2005,

The following business scenario:

Each month, the total quantity of the incoming channel is "totals", in Chinese, English, and other data columns,

The original data is as follows:

Demand:

The data needs to be displayed as one line per year from January to December. Povit's in handy.

Some years, maybe not every month, that is, the generation of dynamic columns.

The first thing to do is build the January-December,

DECLARE @temp NVARCHAR(Max)="'SELECT @temp=COALESCE(@temp,"')+ '['+Monthly+'],' from (SELECT DISTINCTMonthly from  [dms_sourceofbusiness]WHEREYearly='2006') ASet @temp=SUBSTRING(@temp,1,LEN(@temp)-1)

Do you have a query to generate it?
SELECT @temp

Sure enough,

The next step is to use these dynamic columns as columns to generate the data.

DECLARE @sql NVARCHAR(Max)="'SET @sql='SELECT source,yearly'+@temp+'From (SELECT source,yearly,monthly,total from [dbo].[ Dms_sourceofbusiness]) C pivot (MAX (total) for Monthly in ('+ @temp+')) b where yearly="'2006"'and Source like"'%customer walk-in%"''PRINT @sqlEXEC(@sql)

After execution, as shown above, the expected result is the same,

Attention:

Pivot Syntax Structure:

Pivot (A) for B in (C)

A:max (total), which represents the aggregate value to display,

b:monthly, the column header of the original data, is the name of the field whose data is to be converted to columns

C: Dynamic Columns

-------------------------

Scenario Two:

can also be resolved with case, but such dynamic columns become fixed columns,

Application scenario: Month: 1-12 months, week (Monday to Sunday), quarter (Q1 to Q4), etc.

The code is as follows:

 SELECT RTRIM(LTRIM(sob. Source)) Source,sob. Yearly,MAX( Case  when SUBSTRING(SOB. Monthly,6,2)=' on'  ThenSOB. TotalELSE 0 END) as 'M01' ,MAX( Case  when SUBSTRING(SOB. Monthly,6,2)=' Geneva'  ThenSOB. TotalELSE 0 END)'M02' ,MAX( Case  when SUBSTRING(SOB. Monthly,6,2)='Geneva'  ThenSOB. TotalELSE 0 END)'M03' ,MAX( Case  when SUBSTRING(SOB. Monthly,6,2)='Geneva'  ThenSOB. TotalELSE 0 END)'M04' ,MAX( Case  when SUBSTRING(SOB. Monthly,6,2)=' to'  ThenSOB. TotalELSE 0 END)'M05' ,MAX( Case  when SUBSTRING(SOB. Monthly,6,2)=' .'  ThenSOB. TotalELSE 0 END)'M06' ,MAX( Case  when SUBSTRING(SOB. Monthly,6,2)=' -'  ThenSOB. TotalELSE 0 END)'M07' ,MAX( Case  when SUBSTRING(SOB. Monthly,6,2)=' ,'  ThenSOB. TotalELSE 0 END)'M08' ,MAX( Case  when SUBSTRING(SOB. Monthly,6,2)=' the'  ThenSOB. TotalELSE 0 END)'M09' ,MAX( Case  when SUBSTRING(SOB. Monthly,6,2)='Ten'  ThenSOB. TotalELSE 0 END)'M10' ,MAX( Case  when SUBSTRING(SOB. Monthly,6,2)=' One'  ThenSOB. TotalELSE 0 END)'M11' ,MAX( Case  when SUBSTRING(SOB. Monthly,6,2)=' A'  ThenSOB. TotalELSE 0 END)'M12'   from [dbo].[dms_sourceofbusiness]Sob with(NOLOCK)WHERESOB. Yearly=' -'GROUP  bySource,sob. Yearly


Display data format:

Summarize:

Choose a different solution depending on the type,

Using pivot is a common way, but writing SQL or stored procedures is slightly more complicated.

SQL Server column Career pivot use

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.