如何转动这个？ [英] How do I pivot this ?

问题描述

 选择 StockId，转换（ int ，IsNull（PurUnit.ParsedValue， 0 ））ParsedValue 来自 StockDetails  CROSS   APPLY  fn_split_inline_cte（StockDetails.TspNo，' ，'）PurUnit 

 StockId ParsedValue 
 ----------- ----------- 
 20859 4 
 20859 10 
 20859 11 
 22845 4 
 22845 10 
 22845 11 

我想要这个输出需要帮助

 StockId [0] [1] [2] 
 ----------- ----------- --------- -  ----------- 
 20859 4 10 11 
 22845 4 10 11 

解决方案

一种方法是使用CTE作为初始查询的输出（例如请参阅SQL SERVER 2008中的公用表表达式（CTE） [ ^ ]）



然后你可以转动结果（例如参见在SQL查询中使用Pivot的简单方法 [ ^ ]）



我遇到的一个问题不明白你从哪里获得列名（ [0] ， [1] 等）



以下内容适用于您所显示的数据，但我不相信它是您实际执行的内容...例如，实际上只有3个可能的ParsedValue值如果不是你期望的标准出现在每一列中？

;   CTE < span class =code-keyword> as  
（
  select  StockId，转换（ int ，IsNull（PurUnit.ParsedValue， 0 ））ParsedValue 
 来自 StockDetails  CROSS   APPLY  fn_split_inline_cte（StockDetails。 TspNo，' ，'）PurUnit 
）
  SELECT  StockId，[ 4 ]  AS  [ 0 ]，[ 10 ]  AS  [ 1 ]，[ 11 ]  AS  [ 2 ] 
  FROM （
  SELECT  ParsedValue，StockId 
  FROM  CTE 
） as  sourceData 
 PIVOT 
（
 MAX（ParsedValue）
  FOR  ParsedValue  IN （ [ 4 ]，[ 10 ]，[ 11 ]）
） AS  pivotData 

，[1]，[2]应该代表每个StockId的值的等级 - 即每个stockid的前三个值，那么尝试以下

; < span class =code-keyword>  CTE  as  
（
  select  StockId，转换（ int ，IsNull（PurUni） t.ParsedValue， 0 ））ParsedValue 
 来自 StockDetails  CROSS   APPLY  fn_split_inline_cte（StockDetails.TspNo，' ，'）PurUnit 
）
  SELECT  * 
  FROM  
（
 选择 
 Rank（） over （分区 按 StockId 订单  by  ParsedValue） -   1   as  rank，
 StockId，ParsedValue 
 来自 CTE 
） as  sourceData 
 PIVOT 
（
 MAX（ParsedValue）
  FOR 排名 IN （[ 0 ]，[ 1 ]，[ 2 ]）
） AS  pivotData 

select StockId,Convert(int,IsNull(PurUnit.ParsedValue,0))ParsedValue  from StockDetails CROSS APPLY fn_split_inline_cte(StockDetails.TspNo,',') PurUnit

StockId              ParsedValue
-----------          -----------
20859                4
20859                10
20859                11
22845                4
22845                10
22845                11

I want this output help needed

StockId              [0]          [1]              [2]
-----------          -----------  -----------      ----------- 
20859                4            10               11
22845                4            10               11

解决方案

One way would be to use a CTE for the output from your initial query (e.g. see Common Table Expressions(CTE) in SQL SERVER 2008[^])

You can then pivot the results (e.g. see Simple Way To Use Pivot In SQL Query[^])

One problem I didn't understand is where you got the column names from ([0], [1] etc)

The following works with the data you have shown, but I'm not convinced it's what you're actually after ... for example are there really only 3 possible values of ParsedValue and if not what are you expecting the criteria for appearing in each column to be?

;with CTE as 
(
	select StockId,Convert(int,IsNull(PurUnit.ParsedValue,0))ParsedValue  
	from StockDetails CROSS APPLY fn_split_inline_cte(StockDetails.TspNo,',') PurUnit
)
SELECT StockId, [4] AS [0], [10] AS [1], [11] AS [2]
FROM (
    SELECT ParsedValue,StockId
    FROM CTE
) as sourceData
PIVOT
(
    MAX(ParsedValue)
    FOR ParsedValue IN ([4], [10], [11])
)AS pivotData

[EDIT - if those columns [0],[1],[2] are supposed to represent the Rank of the values for each StockId - i.e. top 3 values for each stockid, then try the following

;with CTE as
(
	select StockId,Convert(int,IsNull(PurUnit.ParsedValue,0))ParsedValue  
	from StockDetails CROSS APPLY fn_split_inline_cte(StockDetails.TspNo,',') PurUnit
)
SELECT *
FROM 
(
	select 
	Rank() over (Partition by StockId Order by ParsedValue) - 1 as ranks,
	StockId, ParsedValue
	from CTE
) as sourceData
PIVOT
(
    MAX(ParsedValue)
    FOR ranks IN ([0], [1], [2])
)AS pivotData

这篇关于如何转动这个？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！