sql server 关于数据表横纵切换

最新推荐文章于 2021-10-13 16:56:26 发布

赶在日落之前

最新推荐文章于 2021-10-13 16:56:26 发布

阅读量386

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/lzz781699880/article/details/97390025

版权

数据库专栏收录该内容

45 篇文章 1 订阅

订阅专栏

目的如下：将图1转换为图2 ，就是将因子名称字段里面的值单独提出来作为新的数据表的字段，来提高查询速度

图1

图2

sql如下：

/****** Script for SelectTopNRows command from SSMS  ******/
SET ANSI_WARNINGS OFF;

insert into [lzz_db].[dbo].[t_stock_factor_transpose]
	(	[stock_date]
      ,[stock_code]
      ,[asset_mrq]
      ,[avg_total_market_capitalization_1y]
      ,[betahs300index]
      ,[free_turn]
      ,[m1changepctrm]
      ,[netprofit_ttm]
      ,[nonresisharesjy]
      ,[pe_ttm]
			,[volatility60]
			,[y1volatility]
			,[yoyroe]
      ,[update_time]
      ,[stamp]
	  )


SELECT  [stock_date]
	 ,[stock_code]  
    ,max([asset_mrq])  [asset_mrq]
		,max([avg_total_market_capitalization_1y])   [avg_total_market_capitalization_1y]
		,max([betahs300index])   [betahs300index]
		,max([free_turn])   [free_turn]
		,max([m1changepctrm])   [m1changepctrm]
		,max([netprofit_ttm])   [netprofit_ttm]
		,max([nonresisharesjy])   [nonresisharesjy]
		,max([pe_ttm])   [pe_ttm]
    ,max([volatility60])   [volatility60]
    ,max([y1volatility])   [y1volatility]
    ,max([yoyroe])   [yoyroe]
	  ,getdate()
		,getdate()
FROM
		(
		SELECT  [stock_code]
			,[stock_date]
			,[asset_mrq]
      ,[avg_total_market_capitalization_1y]
      ,[betahs300index]
      ,[free_turn]
      ,[m1changepctrm]
      ,[netprofit_ttm]
      ,[nonresisharesjy]
      ,[pe_ttm]
			,[volatility60]
			,[y1volatility]
			,[yoyroe]
       
		  FROM [lzz_db].[dbo].[t_stock_factor]  
		  PIVOT
		  (
			MAX([factor_value]) FOR [factor_name] in (
			 [asset_mrq]
      ,[avg_total_market_capitalization_1y]
      ,[betahs300index]
      ,[free_turn]
      ,[m1changepctrm]
      ,[netprofit_ttm]
      ,[nonresisharesjy]
      ,[pe_ttm]
			,[volatility60]
			,[y1volatility]
			,[yoyroe]
			) 
	 
		  )tbl

  )tb2
  group by  [stock_code]
      ,[stock_date]
	order by [stock_date],[stock_code]