SQL statement used to generate a crosstab chart

Set quoted_identifier on
Go
Set ansi_nulls on
Go

/* -- Simple and common stored procedure for generating cross tables

Generate a crosstab Chart Based on the specified table name, vertical field, and statistical field.
You can also generate a total in both directions as needed.

Note: If the number of horizontal fields is greater than the number of vertical fields, the vertical fields are automatically exchanged.
If this function is not required, the switch processing section is removed.

-- Producer build 204.06 --*/

/* -- Call example

Exec p_qry 'syscolumns ', 'id', 'colid', 'colid', 'name like ''' % ''',
--*/

Create proc p_qry
@ Tablename sysname, -- table name
@ Sysname on the vertical axis, which is the leftmost column of the crosstab chart.
@ Horizontal axis sysname, -- top column of the crosstab chart
@ Table body content sysname, -- number data field of the cross table
@ Condition varchar (1000), -- Query Processing Condition
@ Whether to add a horizontal total bit. -- if the value is 1, add a horizontal total at the rightmost of the crosstab chart.
@ Bit (BIT) -- add at the bottom of the crosstab chart when the value is 1
As
Declare @ s nvarchar (4000), @ SQL varchar (8000)

-- Normative conditions
Set @ condition = case when @ condition <> ''then'' where ('+ @ condition +') 'else' end

-- Determines whether the horizontal field is greater than the vertical field number. If yes, the vertical field is exchanged.
-- Set @ s = 'Clare @ A sysname
-- If (select case when count (distinct ['+ @ vertical axis +']) <count (distinct ['+ @ horizontal axis +']) then 1 else 0 end
-- From ['+ @ tablename +'] '+ @ condition +') = 1
-- Select @ A = @ vertical axis, @ vertical axis = @ horizontal axis, @ horizontal axis = @'
-- Exec sp_executesql @ s
--, N' @ vertical axis sysname out, @ horizontal axis sysname out'
--, @ Vertical axis out, @ horizontal axis out

-- Generate a cross table processing statement
Set @ s ='
Set @ s = ''''
Select @ s = @ s + '', ['' + Cast (['+ @ horizontal axis +'] As varchar) + ''] = sum (case ['+ @ horizontal axis
+ '] When''' ''' + Cast ([' + @ horizontal axis + '] As varchar) + ''' then ['+ @ table body content +'] else 0 end )''
From ['+ @ tablename +']
'+ @ Condition +'
Group by ['+ @ horizontal axis +']'
Exec sp_executesql @ s
, N' @ s varchar (8000) out'
, @ SQL out

-- Whether to generate the processing of the total field
Declare @ sum1 varchar (200), @ sum2 varchar (200), @ sum3 varchar (200)
Select @ sum1 = case @ add horizontal sum
When 1 then ', [total] = sum ([' + @ table body content + '])'
Else ''end
, @ Sum2 = case @ indicates whether the total number of houses is vertical.
When 1 then '[' + @ vertical axis + '] = case grouping (['
+ @ Vertical axis + ']) when 1 then'' total ''else cast (['
+ @ Vertical axis + '] As varchar) end'
Else '[' + @ vertical axis + ']' End
, @ Sum3 = case @ indicates whether the total number of houses is vertical.
When 1 then 'with rollup'
Else ''end

-- Generate a crosstab chart
Exec ('select' + @ sum2 + @ SQL + @ sum1 +'
From ['+ @ tablename +']
'+ @ Condition +'
Group by ['+ @ vertical axis +'] '+ @ sum3)

Go
Set quoted_identifier off
Go
Set ansi_nulls on
Go

Note:
This stored procedure can only be used for a single table. To generate a stored procedure, first create a complex query, output the standard three columns of the cross table, and then save the output as
View, and finally pass the view to the stored procedure. The result is displayed.