如何从@SQL语句中选择字段到UNPIVOT [英] How to select fields from @SQL statement to UNPIVOT

问题描述

需要从@sql buildup中选择字段以使用 UNPIVOT 命令

Need to select fields from @sql buildup to use the UNPIVOT command

SET @sql = ' SELECT A.BRN_NAME ,A.PROD_SERV , A.COMP_TYPE,
				  P.PROD_SERV_TAT, 
				  COUNT(*) AS OVER_CNT FROM TRIAL
  LEFT JOIN BRANCH C ON A.BRN_NAME = C.BRN_NAME
  LEFT JOIN PROD_SERV P ON A.PROD_SERV = P.PROD_SERV_NAME
  LEFT JOIN PAYMAST B ON A.LNAME = B.LNAME
  AND A.FNAME = B.FNAME '

SET @sql = 'SELECT BRN_NAME, PROD_SERV, COMP_TYPE, 
              PROD_SERV_TAT,OVER_CNT,OPINIONS,OPINION_CNT 
             from ('+  @sql+') AA

   UNPIVOT
     (
      OPINIONS_CNT FOR [OPINIONS] IN ([ACCEPT_TAT], [BEYOND_TAT] , [EXACT_TAT]) 
     ) AB  '

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



这就是我试过的事情

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

This is what I tried

SET @sql = 'SELECT BRN_NAME, PROD_SERV, COMP_TYPE, 
              PROD_SERV_TAT,OVER_CNT,OPINIONS,OPINION_CNT 
             from ('+  @sql+') AA

克服这一挑战的任何帮助



谢谢



我的尝试：



查看了网和其他作品都无济于事

Any help to overcome this challenge

Thanks

What I have tried:

Checked the net and other works all to no avail

推荐答案

由于SQL语言的灵活性，很难从字符串中解析列名。你需要考虑像

这样的东西 - 表别名

- 列别名

- 功能用法

- 标量查询

- 依此类推......



很少有可能对你有用的东西：



将列或列名列表存储在一个单独的字符串中，并在两个地方使用它。



用特殊字符串标识列名字符模式。使用该模式查找列，例如

Because of the flexible nature of SQL language, it would be hard to parse the column names just from a string. You would need to take into account things like
- Table aliases
- Column aliases
- Function usage
- Scalar queries
- and so on...

Few things that might work for you:

Store the list of columns or column names in a separate string and use that in both places.

identify the column name with some special character pattern. Use that pattern to find columns, for example

SELECT A.##BRN_NAME##, 
       A.##PROD_SERV##, 
       A.##COMP_TYPE##,
       P.##PROD_SERV_TAT##,
       COUNT(*) AS ##OVER_CNT##
FROM TRIAL

现在你可以找到 ## 之间的名称。请记住在使用声明之前删除哈希值。



附加：

---------

提取列名并在其他地方使用它时，根据预期的SQL语句，您需要考虑很多事项。正如所说SQL的写作灵活。



但是为了让你开始，请考虑以下示例

Now you can find the names between ##. Just remember to remove the hashes before using the statement.

ADDITION:
---------
When extracting the column names and using it elsewhere, depending on the SQL statements to be expected there are a lot of things you need to take into consideration. As said SQL is flexible in writing.

But to get you started with, consider the following example

CREATE TABLE #test2 (
  col1 int,
  col2 int
);

INSERT INTO #test2 VALUES 
(1,2),
(3,4);

现在，如果我们创建一个从简单的案例中提取列列表的函数，它可能看起来像

Now if we create a function which would extract the column list from a simple case, it could look something like

CREATE FUNCTION ColList(@originalSql nvarchar(max)) RETURNS varchar(max) AS 
BEGIN
   DECLARE @list nvarchar(max),
           @index1 int,
		   @index2 int;

   SET @index1 = 0;
   SET @list = '';
   SET @index1 =CHARINDEX('##', @originalSql, 0) + 2;
   WHILE @index1 > 0 BEGIN
      SET @index2 =CHARINDEX('##', @originalSql, @index1 + 2);
      IF (@list != '') BEGIN
	     SET @list = @list + ', ';
	  END;
      SET @list = @list + SUBSTRING(@originalSql, @index1, @index2 - @index1);
      SET @index1 = CHARINDEX('##', @originalSql, @index2 + 2) ;
	  IF (@index1 > 0 ) BEGIN
	     SET @index1 = @index1 + 2
	  END;
   END;

   RETURN @list
END;

让我们进行测试

Let's have a test

DECLARE
  @sql nvarchar(max),
  @col nvarchar(max)
BEGIN
   SET @sql = 'SELECT t.##Col1##, t.##Col2## FROM #Test2 t';

   SET @col = dbo.ColList(@sql);
   SET @sql = 'SELECT ' + @col + ' FROM (' + REPLACE(@sql, '##','') + ') a';
   PRINT  @sql;
   EXEC (@sql);
END;

执行的查询如下

The query that is executed is as follows

SELECT Col1, Col2 FROM (SELECT t.Col1, t.Col2 FROM #Test2 t) a

但正如所说，那只是首发。例如，您可能需要能够为列列表中使用的列定义别名，依此类推......

But as said, that's just the starters. For example you may need to be able to define an alias for the columns used in the column list, and so on...

这篇关于如何从@SQL语句中选择字段到UNPIVOT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！