需要使用SQL Server SQL转置它 [英] Need to transpose this using SQL server SQL

问题描述

源表

 para_Name para_value in_id id 
 what click_sq 1 43 
 lhs live 1 44 
 rhs live 1 45 
 what clieck_sq 1 46 
 lhs not_live 1 47 
 rhs not_live 1 48 
 what Hover_sq 2 49 
 lhs live 2 50 
 rhs不住2 51 

输出表应该是



< pre lang =text>什么lhs rhs
click_sq live live
clieck_sq not_live not_live
Hover_sq live not_live

我尝试了什么：



pivot我试过但它不工作

  SELECT  what，lhs，rhs 
  FROM （
 选择 Para_Name，Para_Value， id 
 来自 tbl 
 其中 Para_Name  IN （'  what'，'   lhs'，'  rhs'）
）Soruce 
 PIVOT 
（
 min（Para_Value）
  FOR  Para_Name  IN  
（what，lhs，rhs）
） AS  pvt 

解决方案

PIVOT无法管理此数据。 PIVOT正在对相关数据进行聚合，然后将表转过来（聚合的结果）。在你的情况下，你想要聚合和转动的行与任何数据都没有关联（你所拥有的所有关系都在你的脑海中）

出于同样的原因，你无法用有限的方法解决它JOIN的数量（有时你可以绕过PIVOT中聚合的需要）。

要在SQL中连接数据，你必须在SQL中有一些关系......

Source table

para_Name para_value in_id     id
what       click_sq     1      43     
lhs        live         1      44
rhs        live         1      45
what       clieck_sq    1      46
lhs        not_live     1      47
rhs        not_live     1      48
what       Hover_sq     2      49
lhs        live         2      50
rhs        not live     2      51

output table should be

what       lhs         rhs
click_sq   live        live
clieck_sq  not_live    not_live
Hover_sq   live        not_live

What I have tried:

pivot i have tried but its not working

SELECT what,lhs,rhs
FROM (
  Select Para_Name,Para_Value,id
  from tbl
  where Para_Name IN('what','lhs','rhs')
) Soruce
PIVOT
(
  min (Para_Value)
  FOR Para_Name IN
  (what,lhs,rhs)
) AS pvt

解决方案

This data can not be managed by PIVOT. PIVOT is doing an aggregation of related data then turns the table over (the result of the aggregation). In your case the rows you want to aggregate and turn are not related by any data (all the relation you have is in your mind)
For the very same reason, you can not solve it with a finite number of JOINs (as sometimes you may bypass the need for aggregate in PIVOT).
To connect data in SQL you have to have some relation in SQL...

这篇关于需要使用SQL Server SQL转置它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！