SQL Server-仅在结果的一部分上进行数据透视 [英] SQL server - Pivot only on a part of the result
本文介绍了SQL Server-仅在结果的一部分上进行数据透视的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我只需要在查询结果的最后一列中将列放在行中.
I need to put columns in rows only for the last columns of my query's results.
查询是:
select
dpp.CODE_PORTEFEUILLE,
dpr.SOCIETE_GESTION,
lgp.CODE_GERANT,
lgp.DEVISE_GERANT,
lgp.CODE_INTERVENANT
from
GP3DBA.DESCRIPTIF_PORTEFEUILLE dpp
join GP3DBA.DESCRIPTIF_PORT_REPORTING dpr on dpp.CODE_PORTEFEUILLE = dpr.CODE_PORTEFEUILLE
join GP3DBA.LIEN_GERANT_PORTEFEUILLE lgp on dpp.CODE_PORTEFEUILLE = lgp.CODE_PORTEFEUILLE
结果:
所需结果:
我尝试自己做,但没有令人满意的结果.
I have tried to do it myself, but without any satisfying result.
推荐答案
编辑
我看不到这样做的简单方法. 在此示例中,您的实际结果在#tmpResult中.
I don't see an easy way to do this. You actual result is in #tmpResult in this example.
select CODE_PORTEFEUILLE, SOCIETE_GESTION, count(1) as 'number'
into #columnNumber
from #tmpResult
group by CODE_PORTEFEUILLE, SOCIETE_GESTION
declare @columnMaxNumber int;
SELECT @columnMaxNumber = MAX(number)
FROM #columnNumber
declare @tableSql = 'CREATE TABLE #finalResult(
CODE_PORTEFEUILLE int not null,
SOCIETE_GESTION varchar(10) not null'
declare @cnt int = 0;
WHILE @cnt < @columnMaxNumber
BEGIN
set @tableSql = @tableSql + ', CODE_GERANT' + @cnt + ' varchar(2)'
set @tableSql = @tableSql + ', DEVISE_GERANT' + @cnt + ' varchar(3)'
set @tableSql = @tableSql + ', CODE_INTERVENANT' + @cnt + ' varchar(3)'
SET @cnt = @cnt + 1;
END
set @tableSql = @tableSql + ')'
exec @tableSql
然后您只需要在此表中插入数据
then you just have to inject data in this table
declare cursor_portefeuille CURSOR FOR
select * from #tmpResult /*order by fields you want to order by*/
declare @code_portefeuille int, @societe_gestion varchar(3), @code_gerant varchar(2), @device_gerant varchar(3), @code_intervenant varchar(3), @gerant_index int = 0, @last_portefeuille int = 0, @last_societe varchar(3) = ''
OPEN cursor_portefeuille
FETCH NEXT FROM cursor_portefeuille
INTO @code_portefeuille, @societe_gestion, @code_gerant, @device_gerant, @code_intervenant
WHILE @@FETCH_STATUS = 0
BEGIN
if(@last_portefeuille = @code_portefeuille and @last_societe = @societe_gestion)
begin
set @gerant_index = @gerant_index + 1
end
else
begin
set @last_portefeuille = @code_portefeuille
set @last_societe = @societe_gestion
set @gerant_index = 0
end
if exists(select 1 from #finalResult where CODE_PORTEFEUILLE = @code_portefeuille and SOCIETE_GESTION = @societe_gestion)
begin
declare @sqlQuery varchar(512) = 'update #finalResult
set CODE_GERANT' + @gerant_index + ' = @code_gerant,
DEVISE_GERANT' + @gerant_index + ' = @device_gerant,
CODE_INTERVENANT' + @gerant_index + ' = @code_intervenant
WHERE CODE_PORTEFEUILLE = @code_portefeuille and SOCIETE_GESTION = @societe_gestion'
exec @sqlQuery;
end
else
begin
insert into #finalResult(CODE_PORTEFEUILLE, SOCIETE_GESTION, CODE_GERANT0, DEVISE_GERANT0, CODE_INTERVENANT0)
values(@code_portefeuille, @societe_gestion, @code_gerant, @device_gerant, @code_intervenant)
end
FETCH NEXT FROM cursor_portefeuille
INTO @code_portefeuille, @societe_gestion, @code_gerant, @device_gerant, @code_intervenant
END
CLOSE cursor_portefeuille ;
DEALLOCATE cursor_portefeuille ;
SELECT * from #finalResult
/*Don't forget to drop temp tables */
没有尝试,我没有实际的案例,但是我知道逻辑是可行的. 但是您应该避免这样做...运行速度很慢
Didn't try it, I have no actual case at reach but I know the logic works. But you should avoid doing this... It runs fairly slowly
这篇关于SQL Server-仅在结果的一部分上进行数据透视的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文