SQL Server column-to-row refer, sqlbench

SQL Server column-to-row refer, sqlbench

Today, we are doing a data presentation. We use column-to-row conversion. There are multiple methods for column-to-row conversion. The feature is one of them, and Povit is a function that is available only after SQL server 2005,

The following business scenarios:

Total number of purchase channels each month [Total], including Chinese characters, English years, and other data columns,

The raw data is as follows:

Requirements:

We need to display data in a row from January 1, January to August 1, December each year. For example, what should we do? Povit comes in handy

In some years, dynamic columns may not be generated every month.

The first thing we need to do is to build,

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)

Check whether the column is generated? SELECT @ temp

Indeed,

The next step is to use these dynamic columns as the columns for data generation,

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 the execution, it will be the same as the expected display result above,

Note:

Explain syntax structure:

Round (A) for B in (C)

A: Max (Total) indicates the Total value to be displayed,

B: Monthly. The column header of the original data is to convert its data into the column field name.

C: data content of Dynamic Column B

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

Solution 2:

You can also use case when to solve the problem, but such dynamic columns will become fixed columns,

Application scenarios: months: 1-12 months, weeks (Monday to Monday), 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)='01' THEN SOB.Total ELSE 0 END  ) AS 'M01' ,MAX(CASE WHEN SUBSTRING(SOB.Monthly,6,2)='02' THEN SOB.Total ELSE 0 END  ) 'M02' ,MAX(CASE WHEN SUBSTRING(SOB.Monthly,6,2)='03' THEN SOB.Total ELSE 0 END  ) 'M03' ,MAX(CASE WHEN SUBSTRING(SOB.Monthly,6,2)='04' THEN SOB.Total ELSE 0 END  ) 'M04' ,MAX(CASE WHEN SUBSTRING(SOB.Monthly,6,2)='05' THEN SOB.Total ELSE 0 END  ) 'M05' ,MAX(CASE WHEN SUBSTRING(SOB.Monthly,6,2)='06' THEN SOB.Total ELSE 0 END  ) 'M06' ,MAX(CASE WHEN SUBSTRING(SOB.Monthly,6,2)='07' THEN SOB.Total ELSE 0 END  ) 'M07' ,MAX(CASE WHEN SUBSTRING(SOB.Monthly,6,2)='08' THEN SOB.Total ELSE 0 END  ) 'M08' ,MAX(CASE WHEN SUBSTRING(SOB.Monthly,6,2)='09' THEN SOB.Total ELSE 0 END  ) 'M09' ,MAX(CASE WHEN SUBSTRING(SOB.Monthly,6,2)='10' THEN SOB.Total ELSE 0 END  ) 'M10' ,MAX(CASE WHEN SUBSTRING(SOB.Monthly,6,2)='11' 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 ='2015'GROUP BY Source,SOB.Yearly

Display data format:

Summary:

Select different solutions based on different types,

Using explain is a common method, but it is a little complicated to write SQL statements or stored procedures.

Zookeeper