Convertir les lignes en colonnes en utilisant 'Pivot' dans SQL Server

Question

J'ai lu les informations sur les tables pivot de MS et j'ai toujours des problèmes pour obtenir cela correctement.

J'ai une table temporaire qui est créée, disons que la colonne 1 est un numéro de magasin, la colonne 2 est un numéro de semaine et enfin la colonne 3 est un total de quelque chose. De plus, les numéros de semaine sont dynamiques, les numéros de magasin sont statiques.

Magasin      Semaine     xCount
-------    ----     ------
102        1        96
101        1        138
105        1        37
109        1        59
101        2        282
102        2        212
105        2        78
109        2        97
105        3        60
102        3        123
101        3        220
109        3        87

J'aimerais que cela ressorte sous forme de tableau croisé dynamique, comme ceci:

Magasin        1          2          3        4        5        6....
----- 
101        138        282        220
102         96        212        123
105         37        
109

Numéros de magasin sur le côté et les semaines en haut.

Answer 1

Si vous utilisez SQL Server 2005+, alors vous pouvez utiliser la fonction PIVOT pour transformer les données de lignes en colonnes.

Il semble que vous devrez utiliser du SQL dynamique si les semaines sont inconnues mais il est plus facile de voir le code correct en utilisant une version codée en dur initialement.

Tout d'abord, voici quelques définitions de table rapides et des données à utiliser:

CREATE TABLE yt 
(
  [Store] int, 
  [Week] int, 
  [xCount] int
);

INSERT INTO yt
(
  [Store], 
  [Week], [xCount]
)
VALUES
    (102, 1, 96),
    (101, 1, 138),
    (105, 1, 37),
    (109, 1, 59),
    (101, 2, 282),
    (102, 2, 212),
    (105, 2, 78),
    (109, 2, 97),
    (105, 3, 60),
    (102, 3, 123),
    (101, 3, 220),
    (109, 3, 87);

Si vos valeurs sont connues, alors vous coderez la requête en dur:

select *
from 
(
  select store, week, xCount
  from yt 
) src
pivot
(
  sum(xcount)
  for week in ([1], [2], [3])
) piv;

Voir Démo SQL

Ensuite, si vous avez besoin de générer le numéro de semaine de manière dynamique, votre code sera :

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(Week) 
                    from yt
                    group by Week
                    order by Week
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT store,' + @cols + ' from 
             (
                select store, week, xCount
                from yt
            ) x
            pivot 
            (
                sum(xCount)
                for week in (' + @cols + ')
            ) p '

execute(@query);

Voir Démo SQL.

La version dynamique génère la liste des numéros de semaine qui devraient être convertis en colonnes. Les deux donnent le même résultat :

| STORE |   1 |   2 |   3 |
---------------------------
|   101 | 138 | 282 | 220 |
|   102 |  96 | 212 | 123 |
|   105 |  37 |  78 |  60 |
|   109 |  59 |  97 |  87 |

Answer 2

Ceci est pour un nombre dynamique de semaines.

Exemple complet ici: SQL Dynamic Pivot

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Obtenir les valeurs distinctes de la colonne PIVOT
SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(Semaine)
FROM (SELECT DISTINCT Semaine FROM #VentesMagasin) AS Semaines

--Préparer la requête PIVOT en utilisant le dynamique
SET @DynamicPivotQuery = 
  N'SELECT Magasin, ' + @ColumnName + ' 
    FROM #VentesMagasin
    PIVOT(SUM(xNombre) 
          POUR Semaine IN (' + @ColumnName + ')) AS TablePVT'
--Exécuter la requête PIVOT dynamique
EXEC sp_executesql @DynamicPivotQuery

Answer 3

J'ai déjà accompli la même chose en utilisant des sous-requêtes. Donc, si votre table d'origine s'appelait StoreCountsByWeek, et que vous aviez une table séparée qui répertoriait les ID de magasin, cela ressemblerait à ceci :

SELECT StoreID, 
    Week1=(SELECT ISNULL(SUM(xCount),0) FROM StoreCountsByWeek WHERE StoreCountsByWeek.StoreID=Store.StoreID AND Week=1),
    Week2=(SELECT ISNULL(SUM(xCount),0) FROM StoreCountsByWeek WHERE StoreCountsByWeek.StoreID=Store.StoreID AND Week=2),
    Week3=(SELECT ISNULL(SUM(xCount),0) FROM StoreCountsByWeek WHERE StoreCountsByWeek.StoreID=Store.StoreID AND Week=3)
FROM Store
ORDER BY StoreID

Un avantage de cette méthode est que la syntaxe est plus claire et cela simplifie également les jointures avec d'autres tables pour ajouter d'autres champs aux résultats.

Mon expérience personnelle montre que l'exécution de cette requête sur quelques milliers de lignes s'est achevée en moins d'une seconde, et j'avais en fait 7 sous-requêtes. Mais comme mentionné dans les commentaires, cela est plus coûteux en termes de calcul, donc faites attention à utiliser cette méthode si vous prévoyez de l'exécuter sur de grandes quantités de données.

Answer 4

Ceci est ce que vous pouvez faire :

SELECT * 
FROM votreTable
PIVOT (MAX(xCount) 
       POUR Week IN ([1],[2],[3],[4],[5],[6],[7])) AS pvt

DEMO

Answer 5

Je rédige un SP qui pourrait être utile à cette fin, essentiellement ce SP pivote n'importe quelle table et renvoie une nouvelle table pivotée ou simplement l'ensemble de données, voici comment l'exécuter :

Exec dbo.rs_pivot_table @schema=dbo,@table=nom_table,@column=colonne_a_pivoter,@agg='sum([colonne_a_agréger]),avg([une_autre_colonne_a_agréger]),',
        @sel_cols='colonne_a_selectionner1,colonne_a_selectionner2,colonne_a_selectionner1',@new_table=table_retournée_pivotée;

veuillez noter que dans le paramètre @agg, les noms de colonnes doivent être entre '[' et le paramètre doit se terminer par une virgule ','

SP

Create Procedure [dbo].[rs_pivot_table]
    @schema sysname=dbo,
    @table sysname,
    @column sysname,
    @agg nvarchar(max),
    @sel_cols varchar(max),
    @new_table sysname,
    @add_to_col_name sysname=null
Comme
--Exec dbo.rs_pivot_table dbo,##TEMPORAL1,tip_liq,'sum([val_liq]),sum([can_liq]),','cod_emp,cod_con,tip_liq',##TEMPORAL1PVT,'hola';
Commencer

    Déclarer @query varchar(max)='';
    Déclarer @aggDet varchar(100);
    Déclarer @opp_agg varchar(5);
    Déclarer @col_agg varchar(100);
    Déclarer @pivot_col sysname;
    Déclarer @query_col_pvt varchar(max)='';
    Déclarer @full_query_pivot varchar(max)='';
    Déclarer @ind_tmpTbl int; --Indicador de tabla temporal 1=tabla temporal global 0=Tabla fisica

    Créer Table #pvt_column(
        pivot_col varchar(100)
    );

    DECLARE @column_agg table(
        opp_agg varchar(5),
        col_agg varchar(100)
    );

    SI  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@table) AND type in (N'U'))
        Set @ind_tmpTbl=0;
    SINON SI OBJECT_ID('tempdb..'+ltrim(rtrim(@table))) IS NOT NULL
        Set @ind_tmpTbl=1;

    SI  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@new_table) AND type in (N'U')) OU 
        OBJECT_ID('tempdb..'+ltrim(rtrim(@new_table))) IS NOT NULL
    Début
        Set @query='DROP TABLE '+@new_table+'';
        Exec (@query);
    Fin;

    Sélectionner @query='Select distinct '+@column+' From '+(case when @ind_tmpTbl=1 then 'tempdb.' else '' end)+@schema+'.'+@table+' where '+@column+' is not null;';
    Print @query;

    Insérer dans #pvt_column(pivot_col)
    Exec (@query)

    Tant que charindex(',',@agg,1)>0
    Début
        Sélectionner @aggDet=Subchaines(@agg,1,charindex(',',@agg,1)-1);

        Insérer Dans @column_agg(opp_agg,col_agg)
        Valeurs(subchaines(@aggDet,1,charindex('(',@aggDet,1)-1),ltrim(rtrim(replace(subchaines(@aggDet,charindex('[',@aggDet,1),charindex(']',@aggDet,1)-4),')',''))));

        Set @agg=Subchaines(@agg,charindex(',',@agg,1)+1,len(@agg))

    Fin

    Déclarer cur_agg curseur read_only forward_only local static pour
    Sélectionner 
        opp_agg,col_agg
    from @column_agg;

    Ouvrir cur_agg;

    Fetch Prochain De cur_agg
    Into @opp_agg,@col_agg;

    Tant que @@fetch_status=0
    Début

        Déclarer cur_col curseur read_only forward_only local static pour
        Sélectionner 
            pivot_col 
        De #pvt_column;

        Ouvrir cur_col;

        Fetch Prochain De cur_col
        Into @pivot_col;

        Tant que @@fetch_status=0
        Début

            Sélectionner @query_col_pvt='isnull('+@opp_agg+'(case when '+@column+'='+quotename(@pivot_col,char(39))+' then '+@col_agg+
            ' else null end),0) as ['+lower(Replace(Replace(@opp_agg+'_'+convert(varchar(100),@pivot_col)+'_'+replace(replace(@col_agg,'[',''),']',''),' ',''),'&',''))+
                (case when @add_to_col_name is null then espace(0) else '_'+isnull(ltrim(rtrim(@add_to_col_name)),'') end)+']'
            print @query_col_pvt
            Sélectionner @full_query_pivot=@full_query_pivot+@query_col_pvt+', '

            --print @full_query_pivot

            Fetch Prochain De cur_col
            Into @pivot_col;        

        Fin     

        Fermer cur_col;
        Libérer cur_col;

        Fetch Prochain De cur_agg
        Into @opp_agg,@col_agg; 
    Fin

    Fermer cur_agg;
    Libérer cur_agg;

    Sélectionner @full_query_pivot=substring(@full_query_pivot,1,len(@full_query_pivot)-1);

    Sélectionner @query='Select '+@sel_cols+','+@full_query_pivot+' into '+@new_table+' From '+(case when @ind_tmpTbl=1 then 'tempdb.' else '' end)+
    @schema+'.'+@table+' Group by '+@sel_cols+';';

    print @query;
    Exec (@query);

Fin;
GO

Voici un exemple d'exécution :

Exec dbo.rs_pivot_table @schema=dbo,@table=##TEMPORAL1,@column=tip_liq,@agg='sum([val_liq]),avg([can_liq]),',@sel_cols='cod_emp,cod_con,tip_liq',@new_table=##TEMPORAL1PVT;

alors Sélectionner * De ##TEMPORAL1PVT retournerait :