Sql横向数据转换为竖向字段
表1,
表2,
Sql语句如下:
//Join一下,把数据插入虚拟表#T
SELECT i.SN,i.ItemName,t.Sort,t.t
INTO #T
FROM ItemInfo i INNER JOIN
ItemType t ON i.SN=t.FKSN
DECLARE @sql nvarchar(1000)
//动态转向
SET @Sql = 'SELECT ItemName '
SELECT @Sql = @Sql + ',ISNULL(SUM(CASE Sort WHEN '''+Sort+''' THEN t END),0) ['+Sort+']'
FROM (SELECT DISTINCT Sort FROM #T) AS A
SELECT @Sql = @Sql+' FROM [#T] GROUP BY itemName '
//删除虚拟表
SET @Sql=@Sql+' DROP TABLE #T '
EXEC(@sql)
执行结果: