SQL Server column Career pivot use

Today is just the data show, using the column change, there are many ways to change the line, 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 DISTINCT Monthly from  [Dms_sourceofbusiness]where yearly= ' 2006 ') Aset @temp =substring (@temp, 1,len (@temp)-1)

Query whether the column is generated? 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 ' [email protected]+ ' From (SELECT source,yearly,monthly, Total from  [dbo].[ Dms_sourceofbusiness]) C pivot (MAX (total) for Monthly in (' + @temp + ')) b where  

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: Data contents of dynamic column B

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

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) = ' sob ' then. Total ELSE 0 END) as ' M01 ', MAX (case when SUBSTRING (sob. monthly,6,2) = ' sob ' then. Total ELSE 0 END) ' M02 ', MAX (case when SUBSTRING (sob. monthly,6,2) = ' sob ' then. Total ELSE 0 END) ' M03 ', MAX (case when SUBSTRING (sob. monthly,6,2) = ' sob ' then. Total ELSE 0 END) ' M04 ', MAX (case when SUBSTRING (sob. monthly,6,2) = ' sob ' then. Total ELSE 0 END) ' M05 ', MAX (case when SUBSTRING (sob. monthly,6,2) = ' then sob. Total ELSE 0 END) ' M06 ', MAX (case when SUBSTRING (sob. monthly,6,2) = ' then sob. Total ELSE 0 END) ' M07 ', MAX (case when SUBSTRING (sob. monthly,6,2) = ' then sob '. Total ELSE 0 END) ' M08 ', MAX (case when SUBSTRING (sob. monthly,6,2) = ' sob ' then. Total ELSE 0 END) ' M09 ', MAX (case when SUBSTRING (sob. monthly,6,2) = ' ten ' then sob. Total ELSE 0 END) ' M10 ', MAX (case when SUBSTRING (sob. monthly,6,2) = ' one ' then sob. Total ELSE 0 END) ' M11 ', MAX (case when SUBSTRING (sob. monthly,6,2) = ' 12' Then sob. Total ELSE 0 END) ' M12 ' from [dbo]. [Dms_sourceofbusiness] SOB with (NOLOCK) WHERE sob. Yearly = ' GROUP by Source,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