Mssql表数据行转列

最新推荐文章于 2023-05-16 15:02:20 发布

满嘴地瓜腔

最新推荐文章于 2023-05-16 15:02:20 发布

阅读量419

点赞数

分类专栏： Mssql

本文链接：https://blog.csdn.net/diguaqiang/article/details/45933021

版权

Mssql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

CREATE TABLE [LG_GJMX] (
	[ID] [varchar] (50) NULL ,
	[作业单位] [varchar] (50) NULL ,
	[工价] [numeric](18, 0) NULL 
) 

DECLARE @SQL	NVARCHAR(4000)
DECLARE @ZYDW	VARCHAR(50)

SELECT DISTINCT(ID) INTO #TEMP FROM LG_GJMX
SELECT * INTO #TEMP1 FROM LG_GJMX

DECLARE CURZYDW CURSOR FOR 
SELECT DISTINCT(作业单位) FROM LG_GJMX
OPEN CURZYDW
	FETCH NEXT FROM CURZYDW INTO @ZYDW
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @SQL = 'ALTER TABLE #TEMP ADD ' + @ZYDW + ' NUMERIC(18,3)'
		EXECUTE SP_EXECUTESQL @SQL
		
		SET @SQL = 'UPDATE #TEMP
			    SET #TEMP.' + @ZYDW + ' = #TEMP1.工价
			    FROM #TEMP,#TEMP1
			    WHERE #TEMP.ID = #TEMP1.ID
			    AND #TEMP1.作业单位=''' + @ZYDW + ''''
		EXECUTE SP_EXECUTESQL @SQL

		FETCH NEXT FROM CURZYDW INTO @ZYDW
	END 	
CLOSE CURZYDW
DEALLOCATE CURZYDW

SELECT * FROM #TEMP
DROP TABLE #TEMP
DROP TABLE #TEMP1