SQLSERVER 动态转列，生成动态视图

SQLSERVER 动态转列，生成动态视图

创建测试数据

create table test_1(col1 varchar(10),col2 varchar(10),col3 varchar(10));

insert into test_1 values('a','b','c');
insert into test_1 values('q','w','z');
insert into test_1 values('o','p','q');

 

创建存储过程

CREATE PROC SP_ACTIVE_SQL
AS
BEGIN
  -- DECLARE THE VARIABLES TO STORE THE VALUES RETURNED BY FETCH.
  DECLARE @V_COL1 VARCHAR(10), @V_COL2 VARCHAR(10),@V_STRING1 VARCHAR(8000),@V_STRING2 VARCHAR(8000),@V_SQL VARCHAR(8000)

  DECLARE CONTACT_CURSOR CURSOR FOR SELECT COL1,COL2 FROM TEST_1
  OPEN CONTACT_CURSOR
  FETCH NEXT FROM CONTACT_CURSOR
  INTO @V_COL1, @V_COL2
  -- CHECK @@FETCH_STATUS TO SEE IF THERE ARE ANY MORE ROWS TO FETCH.
  WHILE @@FETCH_STATUS = 0
  BEGIN
      IF(@V_STRING1 IS NULL) 
          BEGIN 
              SET @V_STRING1 =  '''' + @V_COL2+ ''' AS ' + @V_COL1
          END 
      ELSE 
          BEGIN 
              SET @V_STRING1 = @V_STRING1 + ','''+ @V_COL2+''' AS '+@V_COL1
          END 

      IF(@V_STRING2 IS NULL) 
          BEGIN 
              SET @V_STRING2 =  'CASE WHEN COL2=' + @V_COL1 +' THEN COL3 ELSE NULL END AS '+ @V_COL1
          END 
      ELSE 
          BEGIN 
              SET @V_STRING2 = @V_STRING2 +', CASE WHEN COL2=' + @V_COL1 +' THEN COL3 ELSE NULL END AS '+ @V_COL1
          END       

     -- THIS IS EXECUTED AS LONG AS THE PREVIOUS FETCH SUCCEEDS.
    FETCH NEXT FROM CONTACT_CURSOR
    INTO @V_COL1, @V_COL2
  END
  CLOSE CONTACT_CURSOR
  DEALLOCATE CONTACT_CURSOR

  --生成SQL语句
  SET @V_SQL='SELECT T1.*,' + @V_STRING2+ ' FROM (SELECT DISTINCT COL2,COL3 FROM TEST_1) T1,(SELECT ' + @V_STRING1 + ') T'
  --PRINT @V_SQL
  EXEC(@V_SQL) 
END 

存储过程作为表进行查询

exec SP_ACTIVE_SQL