sql-server - 基于复杂静态 SQL 迭代所有列的动态 SQL

Question

我有一个静态 SQL 查询，它找出空白计数和数据类型（无论是数字还是字母数字等）。以下是查询：

SELECT
case when Pattern = '' then 'BLANK' else Pattern end AS Pattern,
LEN(case when Pattern = '' then 'BLANK' else Pattern end) Length,
COUNT(*) AS Count FROM
     ( SELECT REPLACE(REPLACE( REPLACE( REPLACE( REPLACE(REPLACE( REPLACE( REPLACE( REPLACE(      
     REPLACE( REPLACE( REPLACE( REPLACE(REPLACE( REPLACE( REPLACE( REPLACE(REPLACE( REPLACE( 
     REPLACE( REPLACE(REPLACE( REPLACE( 
     REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE    
     (REPLACE(REPLACE(REPLACE(REPLACE(UPPER(col_name),'A','A'),'B','A') ,'C','A') ,'D','A') 
     ,'E','A'),'F','A') ,'G','A') ,'H','A') ,'I','A'),'J','A') ,'K','A') ,'L','A') ,'M','A')     
     ,'N','A') ,'O','A') ,'P','A')
     ,'Q','A'),'R','A') ,'S','A') ,'T','A') ,'U','A') ,'V','A') ,'W','A') ,'X','A') ,'Y','A')    
     ,'Z','A'),'8','N'),'7','N'),'6','N'),'5','N'),'4','N'),'3','N')   
     ,'2','N'),'1','N'),'0','N'),'9','N')  
 AS Pattern FROM table_name) A  GROUP BY Pattern;

现在，假设我有一张下表

CREATE TABLE Data (
 Column1 varchar(50),
 Column2 varchar(50),
 Column3 varchar(50),
 Column4 varchar(50),
   )
INSERT INTO Data
 (Column1, Column2, Column3, Column4)
VALUES 
 (NULL, NULL, 'ABC123', 'abc '),
 ('xyz', NULL, 'MNO300', 'XYZ123 ')

现在我想要如下输出：

Count_Pat_1 --> Count is of only one data type i.e. either Alphabetic or 
Numeric
Count_Part_2 --> Count is of mixed data type i.e. contains both alphabet and 
number
Blank --> Count of NULL values

Col_name    Count_Pat_1    Count_Pat_2  Blank
Column1          1               0       1        
Column2          0               0       2
Column3          0               2       0
Column4          1               1       0 

如何生成上表？显然，我需要求助于动态 SQL，其中需要将整个 sql 字符串设置为变量。以下是我的方法：

DECLARE @sql nvarchar(max)
SET @sql = N''

SELECT @sql = @sql + CASE WHEN @stm = N'' THEN N'' ELSE N',' END +
  '('+
  '''' + c.name + ''', '+
  'CASE '+
   'WHEN ' + QUOTENAME(c.name) + ' IS NULL THEN 1 ' +
   'ELSE 0 ' +
  'END, ' +
  'CASE '+
   'WHEN ' + QUOTENAME(c.name) + ' IS NOT NULL THEN LEN(' + QUOTENAME(c.name) + ') - ** the above SQL 
   script ***
  FROM sys.columns c
  JOIN sys.tables t ON c.object_id = t.object_id
  WHERE t.name = 'Data'

但是在 ** 指示的位置插入上述 SQL 脚本将无法正确给出输出。

因此，任何提示将不胜感激。SQL Server 版本：2016

标签： sql-server