SQL Server column Career pivot use

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