Sqlpivot获取部分数据nad select as rows列 [英] Sqlpivot get part of data nad select as rows column

问题描述

 Q年Q Q Q4 
 2000 200 456 45 35 
 2000 200 435 435 545 
 2000 200 454 43 45 
 2000 200 45 435 4 
 2001 300 4 43 545 
 2001 300 43 4 56 
 2001 300 4 4 4 
 2001 300 43 43 6 
 2001 300 4 56 34 
 2002 400 4 34 43 
 2002 400 43 45 6 
 2002 400 4 45 4 
 2002 400 43 34 54 
 2002 400 45 45 6 

i有以上数据，但我希望它有不同的格式

 Year1 Q1 Q2 Q3 Q4 year Q1 Q2 Q3 Q4 Q Q Q4 Q4 
 2000 200 456 45 35 2001 300 4 43 545 2002 400 4 34 43 
 2000 200 435 435 545 2001 300 43 4 56 2002 400 43 45 6 
 2000 200 454 43 45 2001 300 4 4 4 2002 400 4 45 4 
 2000 200 45 435 4 2001 300 43 43 6 2002 400 43 34 54 

我尝试了什么：



i我什么都不知道。

  SELECT  *  FROM  EDELGIVE_BUDGETSUMARY A 
 
  left   join  EDELGIVE_BUDGETSUMARY B  on  a.BUDGET_YEAR = b.BUDGET_YEAR  WHERE （a.BUDGET_YEAR = b.BUDGET_YEAR）

解决方案

你不需要PIVOT，但是你可以使用Common Table Expression来实现。



第一个问题你你有没有定义你如何决定2000

 2000 200 456 45 35 

的值应该与

 2001 300 4 43 545 

看起来好像你正在使用2000年的第1行与2001年的第1行和2002年的第1行等，但你没有什么可以定义什么是每组的第1行，第2行 - 你真的需要在你的基表上使用某种ID列。



我用这样创建了你的样本数据： pre lang =SQL> 创建 table #radhasapna（[Year] int ，Q int ，Q2 int ，Q3 int ，Q4 int ）
insert into #radhasapna（[Year]，Q，Q2，Q3， Q4）值
（ 2000 ， 200 ， 456 ， 45 ， 35 ），
（ 2000 ， 200 ， 435 ， 435 ， 545 ），
（ 2000 ， 200 ， 454 ， 43 ， 45 ），
（ 2000 ， 200 ， 45 ， 435 ， 4 ），
（ 2001 ， 300 ， 4 ， 43 ， 545 ），
（ 2001 ， 300 ， 43 ， 4 ， 56 ），
（ 2001 ， 300 ， 4 ， 4 ， 4 ），
（ 2001 ， 300 ， 43 ，< span class =code-digit> 43 ， 6 ），
（ 2001 ， 300 ， 4 ， 56 ， 34 ），
（ 2002 ， 400 ， 4 ， 34 ， 43 ），
（ 2002 ， 400 ， 43 ， 45 ， 6 ），
（ 2002 ， 400 ， 4 ， 45 ， 4 ），
（ 2002 ， 400 ， 43 ， 34 ， 54 ），
（ 2002 ， 400 ， 45 ， 45 ， 6 ）

从那里我可以得到你想要的结果，每年给每行一个ROW_NUMBER（），然后只对结果进行多次连接。

;   cte  as  
（
  SELECT  ROW_NUMBER（）< span class =code-keyword> OVER （ PARTITION   BY  [年]  ORDER   BY  [Year]） as  r，
 [年]，Q，Q2，Q3，Q4 
  FROM  #radhasapna 
）
 选择 a。*，b。*，c。* 
 来自 cte a 
  left   outer   join  cte b 上的pan class =code-keyword> a。[年] = b。[年] 和 ar = br 
  left  外部  join  cte c  on  a。[年] = c。[年] 和 ar = cr 

我不得不放一个ORDER BY进入Window函数但不允许常量，所以我再次使用[Year] ...这意味着我得到正确结果的事实是纯粹的好运！



但如果我（你！）像这样定义基表

  create   table  #radhasapna（id  int   identity （ 1 ， 1 ），[年]  int ，Q  int ，Q2  int ，Q3  int ，Q4  int ）

并使用

;  with  cte  as  
（
  SELECT  ROW_NUMBER（） OVER （ PARTITION   BY  [年份   ORDER   BY  id  ） as  r，
 [年]，Q，Q2，Q3，Q4 
  FROM ＃ radhasapna 
）
 选择 a。*，b。*，c。* 
 来自 cte a 
  left   outer  加入 cte b   a。[年] = b。[年] 和 ar = br 
  left  外部  join  cte c  on  a。[Year] = c。[年份] 和 ar = cr 

然后您的结果有保证



------------------------------------------------- ----------------------------

- OP指出年数可能会有所不同。

----------------------------------------- ------------------------------------



如果年数可能不同，您将需要使用动态SQL，如下所示：

1.设置一些初始值，即最小年份（ @ minY ）和年数（ @years ）和动态sql的基础（ @sql ）

 声明  @ years 整数=（ SELECT  COUNT（ DISTINCT  [Year]） FROM  #radhasapna）
 声明  @minY   nvarchar （ 10 ）=（ SELECT  CAST（MIN （[年]） as   nvarchar ） FROM  #radhasapna）
 
 声明  @ sql   nvarchar （max）
  set   @ sql  = ' ; cte为（
 SELECT ROW_NUMBER（）OVER（PARTITION BY [Year] ORDER BY id）为r，
 [年]，Q，Q2，Q3，Q4 
来自#radhasapna）
 SELECT' 

2。用

 选择 a。*，b。*，c。* 

替换

 选择 a1。*，a2。*，a3。*等

注意我现在使用的是a1，a2 ，a3等作为表别名...

 声明  @ i   int  =  1  
  WHILE   @ i < =  @ years  
  begin  
  set   @ sql  =  @sql  + stuff（'  aXX'， 2 ， 2 ， @ i ）+ ' 。*' 
 如果  @ i <  @ years  
 开始 
  set   @ sql  =  @sql  + ' ，' 
  end  
  set   @ i  =  @ i  +  1  
  end  

如果我 PRINT @sql 在这个阶段我得到这个

;用cte as（
 SELECT ROW_NUMBER（）OVER（PARTITION BY [Year] ORDER BY id）为r，
 [Year]，Q ，Q2，Q3，Q4 
来自#radhasapna）
 SELECT a1。*，a2。*，a3。*，a4。*，a5。* 

3.弄清楚如何进行连接...

  set   @sql  =  @ sql  + ' 来自cte a1' 
 
  set   @ i  =  2  
  WHILE   @ i < =  @ years  
 开始 
 声明  @ s   nvarchar （ 10 ）= stuff（'  aXX'， 2 ， 2 ， @ i ）
 声明  @ prevS   nvarchar （ 10 ）= stuff（'  aXX'， 2 ， 2 ， @ i-1 ）
  set   @ sql  =  @ sql  + '  left outer join cte' +  @ s  '  on' +  @ s  + ' 。[Year] =' +  @ prevS  + ' 。[Year] + 1和a1.r =' +  @ s  + ' 。r' 
 < span class =code-keyword> set   @ i  =  @ i  +  1  
  end  

再次，如果我 PRINT @sql 现在已经添加到结尾

 from cte a1 
 left out join cte a2 on a2。[Year] = a1。[Year] + 1 and a1.r = a2.r 
 left outer join cte a3 on a3。[年] = a2。[年] + 1和a1.r = a3.r 
左外连接c4 a4 a4。[年] = a3。[年] + 1和a1.r = a4.r 
在a5上留下外连接cte a5。[年] = a4。[年] + 1和a1.r = a5.r 

4。剩下要做的就是在SQL的结尾放一个 WHERE 子句和一个 ORDER BY 子句并运行它

  set   @ sql  =  @sql  + ' 其中a1。[年] =' +  @ minY  + ' 按a1.r'排序 
 
  EXEC  sp_executesql  @ sql  

Year	Q	Q2	Q3	Q4	
2000	200	456	45	35	
2000	200	435	435	545	
2000	200	454	43	45	
2000	200	45	435	4	
2001	300	4	43	545	
2001	300	43	4	56	
2001	300	4	4	4	
2001	300	43	43	6	
2001	300	4	56	34	
2002	400	4	34	43	
2002	400	43	45	6	
2002	400	4	45	4	
2002	400	43	34	54	
2002	400	45	45	6

i have above data but i want it in different format

Year1	Q1	Q2	Q3	Q4	year	Q1	Q2	Q3	Q4	Year	Q	Q2	Q3	Q4
2000	200	456	45	35	2001	300	4	43	545	2002	400	4	34	43
2000	200	435	435	545	2001	300	43	4	56	2002	400	43	45	6
2000	200	454	43	45	2001	300	4	4	4	2002	400	4	45	4
2000	200	45	435	4	2001	300	43	43	6	2002	400	43	34	54

What I have tried:

i am not getting any idea.

SELECT * FROM EDELGIVE_BUDGETSUMARY A 

left join EDELGIVE_BUDGETSUMARY B on a.BUDGET_YEAR = b.BUDGET_YEAR WHERE ( a.BUDGET_YEAR = b.BUDGET_YEAR )

解决方案

You don't need a PIVOT for this, but you can do it with a Common Table Expression.

The first problem you have is that you have not defined how you've decided that the values from 2000

2000	200	456	45	35

should be displayed on the same line as

2001	300	4	43	545

It looks as if you are using "line 1 from 2000 goes with line 1 of 2001 and line 1 of 2002" etc, but you have nothing to defined what is "line 1", "line 2" of each group - you really need some sort of ID column on your base table.

I created your sample data with this:

create table #radhasapna ([Year] int, Q int, Q2 int, Q3 int, Q4 int)
insert into #radhasapna ([Year] , Q , Q2 , Q3 , Q4 ) values
(2000,	200,	456,	45,	35	),
(2000,	200,	435,	435,	545	),
(2000,	200,	454,	43,	45	),
(2000,	200,	45,	435,	4	),
(2001,	300,	4,	43,	545	),
(2001,	300,	43,	4,	56	),
(2001,	300,	4,	4,	4	),
(2001,	300,	43,	43,	6	),
(2001,	300,	4,	56,	34	),
(2002,	400,	4,	34,	43	),
(2002,	400,	43,	45,	6	),
(2002,	400,	4,	45,	4	),
(2002,	400,	43,	34,	54	),
(2002,	400,	45,	45,	6)

From there I was able to get the results you wanted by giving each row in each year a ROW_NUMBER() and then just doing multiple joins to the results.

;with cte as
(
	SELECT ROW_NUMBER() OVER (PARTITION BY [Year] ORDER BY [Year]) as r, 
		[Year], Q, Q2, Q3, Q4
	FROM #radhasapna
)
select a.*, b.*, c.*
from cte a 
left outer join cte b on a.[Year] = b.[Year] and a.r = b.r
left outer join cte c on a.[Year] = c.[Year] and a.r = c.r

I had to put an ORDER BY into the Window function but a constant is not allowed, so I used [Year] again ... which means the fact I got the correct results is pure good luck!

BUT if I (you!) define the base table like this

create table #radhasapna (id int identity(1,1), [Year] int, Q int, Q2 int, Q3 int, Q4 int)

and use

;with cte as
(
	SELECT ROW_NUMBER() OVER (PARTITION BY [Year] ORDER BY id) as r,
		 [Year], Q, Q2, Q3, Q4
	FROM #radhasapna
)
select a.*, b.*, c.*
from cte a 
left outer join cte b on a.[Year] = b.[Year] and a.r = b.r
left outer join cte c on a.[Year] = c.[Year] and a.r = c.r

then your results are guaranteed

-----------------------------------------------------------------------------
[EDIT] - OP Pointed out that the number of years could vary.
-----------------------------------------------------------------------------

You will need to use dynamic SQL if the number of years could vary, something like this:
1. Set up some initial values, the "smallest" year (@minY) and the number of years (@years) and the basis for our dynamic sql (@sql)

declare @years integer = (SELECT COUNT(DISTINCT [Year]) FROM #radhasapna)
declare @minY nvarchar(10) = (SELECT CAST(MIN([Year]) as nvarchar) FROM #radhasapna)

declare @sql nvarchar(max)
set @sql = ';with cte as (
	SELECT ROW_NUMBER() OVER (PARTITION BY [Year] ORDER BY id) as r,
		 [Year], Q, Q2, Q3, Q4
	FROM #radhasapna )
SELECT '

2. Replace what I had earlier as

select a.*, b.*, c.*

with

select a1.*, a2.*, a3.*, etc

Note I'm now using a1, a2, a3, etc as the table aliases...

declare @i int = 1
WHILE @i <= @years
begin
	set @sql = @sql + stuff('aXX', 2,2, @i) + '.*'
	if @i < @years
	begin
		set @sql = @sql + ','
	end 
	set @i = @i + 1
end

If I PRINT @sql at this stage I get this

;with cte as (
	SELECT ROW_NUMBER() OVER (PARTITION BY [Year] ORDER BY id) as r,
		 [Year], Q, Q2, Q3, Q4
	FROM #radhasapna )
SELECT a1.*,a2.*,a3.*,a4.*,a5.*

3. Work out how to do the joins...

set @sql = @sql + ' from cte a1 '

set @i = 2
WHILE @i <= @years
begin
	declare @s nvarchar(10) = stuff('aXX', 2,2, @i)
	declare @prevS nvarchar(10) = stuff('aXX',2,2,@i-1)
	set @sql = @sql + 'left outer join cte ' + @s + ' on ' + @s + '.[Year] = ' + @prevS + '.[Year] + 1 and a1.r = ' + @s + '.r '
	set @i = @i + 1
end

Again, if I PRINT @sql now then this has been added to the end

from cte a1 
left outer join cte a2 on a2.[Year] = a1.[Year] + 1 and a1.r = a2.r 
left outer join cte a3 on a3.[Year] = a2.[Year] + 1 and a1.r = a3.r 
left outer join cte a4 on a4.[Year] = a3.[Year] + 1 and a1.r = a4.r 
left outer join cte a5 on a5.[Year] = a4.[Year] + 1 and a1.r = a5.r 

4. All that is left to do is put a WHERE clause and an ORDER BY clause on the end of the sql and run it

set @sql = @sql + 'where a1.[Year] = ' + @minY + ' order by a1.r'

EXEC sp_executesql @sql

这篇关于Sqlpivot获取部分数据nad select as rows列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！