Pivot à plusieurs colonnes en T-SQL
je travaille avec un tableau où il y a plusieurs lignes que j'ai besoin de pivoter en colonnes. Donc le pivot est la solution parfaite pour cela, et fonctionne bien quand tout ce dont j'ai besoin est un domaine. Je dois retourner plusieurs champs basés sur le pivot. Voici le pseudo code avec les détails supprimés:
SELECT
field1,
[1], [2], [3], [4]
FROM
(
SELECT
field1,
field2,
(ROW_NUMBER() OVER(PARTITION BY field1 ORDER BY field2)) RowID
FROM tblname
) AS SourceTable
PIVOT
(
MAX(field2)
FOR RowID IN ([1], [2], [3], [4])
) AS PivotTable;
la syntaxe ci-dessus fonctionne brillamment, mais que dois-je faire quand j'ai besoin d'obtenir des informations supplémentaires trouvées dans field3, field4....?
5 réponses
Rewrite using MAX (CASE...) et GROUP BY:
select
field1
, [1] = max(case when RowID = 1 then field2 end)
, [2] = max(case when RowID = 2 then field2 end)
, [3] = max(case when RowID = 3 then field2 end)
, [4] = max(case when RowID = 4 then field2 end)
from (
select
field1
, field2
, RowID = row_number() over (partition by field1 order by field2)
from tblname
) SourceTable
group by
field1
de là, vous pouvez ajouter dans field3, field4, etc.
Je ne sais pas si vous utilisez MS SQL Server, mais si vous le faites... Vous voudrez peut-être jeter un coup d'oeil à la fonctionnalité D'application croisée du moteur. Fondamentalement, il vous permettra d'appliquer les résultats D'un UDF table-valued à un ensemble de résultats. Cela vous exigerait de mettre votre requête de pivot dans un jeu de résultats de valeur de table.
http://weblogs.sqlteam.com/jeffs/archive/2007/10/18/sql-server-cross-apply.aspx
enveloppez votre instruction sql avec quelque chose comme:
select a.segment, sum(field2), sum(field3)
from (original select with case arguments) a
group by a.segment
il devrait s'effondrer vos résultats dans une rangée, groupés sur field1.
Le truc pour faire plusieurs pivots sur un row_number est de modifier cette séquence de nombres de ligne pour stocker à la fois la séquence et le numéro de champ. Voici un exemple qui fait ce que vous voulez avec plusieurs énoncés de PIVOT.
-- populate some test data
if object_id('tempdb..#tmp') is not null drop table #tmp
create table #tmp (
ID int identity(1,1) not null,
MainField varchar(100),
ThatField int,
ThatOtherField datetime
)
insert into #tmp (MainField, ThatField, ThatOtherField)
select 'A', 10, '1/1/2000' union all
select 'A', 20, '2/1/2000' union all
select 'A', 30, '3/1/2000' union all
select 'B', 10, '1/1/2001' union all
select 'B', 20, '2/1/2001' union all
select 'B', 30, '3/1/2001' union all
select 'B', 40, '4/1/2001' union all
select 'C', 10, '1/1/2002' union all
select 'D', 10, '1/1/2000' union all
select 'D', 20, '2/1/2000' --union all
-- pivot over multiple columns using the 1.1, 1.2, 2.1, 2.2 sequence trick
select
MainField,
max([1.1]) as ThatField1,
max([1.2]) as ThatOtherField1,
max([2.1]) as ThatField2,
max([2.2]) as ThatOtherField2,
max([3.1]) as ThatField3,
max([3.2]) as ThatOtherField3,
max([4.1]) as ThatField4,
max([4.2]) as ThatOtherField4
from
(
select x.*,
cast(row_number() over (partition by MainField order by ThatField) as varchar(2)) + '.1' as ThatFieldSequence,
cast(row_number() over (partition by MainField order by ThatField) as varchar(2)) + '.2' as ThatOtherFieldSequence
from #tmp x
) a
pivot (
max(ThatField) for ThatFieldSequence in ([1.1], [2.1], [3.1], [4.1])
) p1
pivot (
max(ThatOtherField) for ThatOtherFieldSequence in ([1.2], [2.2], [3.2], [4.2])
) p2
group by
MainField
il est possible de pivoter sur plusieurs colonnes, mais vous devez être prudent sur la réutilisation de la colonne de pivot à travers plusieurs pivots. Voici un bon article de blog sur le sujet:
http://pratchev.blogspot.com/2009/01/pivoting-on-multiple-columns.html