列名在一个表中存储
我有一个表,结构如下:
表名 TABLE1
列名: ACS1 ACS2 ACS3......ACS20
数据 X1 X2 X3 .... AE
表2 TABLE2
列名 ACS1 FEL1 FEL2
其中 “ACS1”的值与TABLE1中“ACS1 "的值一样,但是,FEL1,FEL2中存在是表TABLE1中列的名字。
举例: ACS1 FEL1 FEL2
X1 ACS4 ACS10
X2 ACS4 ACS1
X3 ACS5 ACS22
我需要的是根据TABLE2中指定的字段,得到该字段对应的值。
结果如下:
ACS1 FEL1 FEL2 ACS4 ACS10
X1 ACS4 ACS10 X4 X10
X2 ACS4 ACS1 X4 X10
X3 ACS5 ACS22 X5 X22
如何做?
[解决办法]
CREATE TABLE TABLE1(QU1 VARCHAR(100),STDT VARCHAR(100),SUDT VARCHAR(100),SFDT VARCHAR(100),UFDT VARCHAR(100))CREATE TABLE TABLE2(QU1 VARCHAR(100),FEL1 VARCHAR(100),FEL2 VARCHAR(100))GOINSERT INTO TABLE1SELECT '3-SA','20120101','20120504','20120708','20120910'UNION ALLSELECT '1-HA','20121010','20120401','20120727','20120501'UNION ALLSELECT '2-TD','20121001','20120602','20121231','20120512' UNION ALLSELECT '3-HA','20121101','20120805','20120110','20120807' INSERT INTO TABLE2SELECT '3-SA','SUDT','UFDT'UNION ALLSELECT '1-HA','STDT','SUDT'UNION ALLSELECT '2-TD','SUDT','SFDT'UNION ALLSELECT '3-HA','SFDT','STDT'SELECT * FROM TABLE1SELECT * FROM TABLE2GOCREATE TABLE #TAB(P1 VARCHAR(100),P2 VARCHAR(100),P3 VARCHAR(100),P4 VARCHAR(100),P5 VARCHAR(100))DECLARE @SQL VARCHAR(8000),@P1 VARCHAR(100),@P2 VARCHAR(100),@P3 VARCHAR(100)EXEC (@SQL)DECLARE CUR_SOR CURSOR FOR SELECT * FROM TABLE2OPEN CUR_SORFETCH NEXT FROM CUR_SOR INTO @P1,@P2,@P3WHILE @@FETCH_STATUS=0BEGIN SET @SQL = 'INSERT INTO #TAB ' + ' SELECT '''+@P1+''' ,''' + @P2 + ''',''' + @P3+''','+ @P2+',' + @P3 +' FROM TABLE1 WHERE QU1='''+@P1+''' ' EXEC (@SQL) FETCH NEXT FROM CUR_SOR INTO @P1,@P2,@P3ENDCLOSE CUR_SORDEALLOCATE CUR_SORSELECT * FROM #TABDROP TABLE #TABGODROP TABLE TABLE1DROP TABLE TABLE2