SQL Server Pivot plusieurs colonnes basées sur une colonne

J'ai la suite de la source et de la destination des tables dans sql server 2008R2. Comment puis-je faire de pivot(s) en TSQL pour se rendre à la destination de la source.

SourceTbl

empId    empIndex    empState    empStDate    empEndDate
========================================================
10        1           AL          1/1/2012     12/1/2012
10        2           FL          2/1/2012     2/1/2013
15        1           FL          3/20/2012    1/1/2099

DestTbl

empId    empState1  empState1StDate    empState1EndDt    empState2  empState2StDate    empState2EndDt
=========================================================================================================
10        AL         1/1/2012           12/1/2012         FL         2/1/2012           2/1/2013
15        FL         3/20/2012          1/1/2099          NULL       NULL               NULL

En espérant que le empIndex sera en quelque sorte aider dans le pivot.

pivot est également connu comme le transformer en msaccess. votre question est unique en ce qu'il a également le texte (pas entiers) dans les cellules qui en résultent. la fonction d'agrégation a encore à s'appliquer, dans ce cas, MIN() devrait faire amende sur les valeurs de Texte, même lorsqu'il est à seulement 1 valeur de texte.

OriginalL'auteur BJ Rocking | 2013-08-02

  1. 23

    Depuis que vous êtes à l'aide de SQL Server il y a plusieurs façons différentes que vous pouvez convertir les lignes en colonnes. Vous pouvez utiliser une fonction d'agrégation avec une expression case:

    select empid,
  max(case when empindex = 1 then empstate end) empState1,
  max(case when empindex = 1 then empStDate end) empStDate1,
  max(case when empindex = 1 then empEndDate end) empEndDate1,
  max(case when empindex = 2 then empstate end) empState2,
  max(case when empindex = 2 then empStDate end) empStDate2,
  max(case when empindex = 2 then empEndDate end) empEndDate2
from sourcetbl
group by empid;

    Voir SQL jouer avec la Démo.

    Si vous souhaitez utiliser la fonction de PIVOT pour obtenir le résultat, alors je vous recommande d'abord unpivoting les colonnes empState, empStDate et empEndDate si vous avez plusieurs lignes. Vous pouvez utiliser le UNPIVOT de la fonction ou de la CROIX s'APPLIQUENT à convertir les données, le code sera:

    select empid, col+cast(empindex as varchar(10)) col,  value
from sourcetbl
cross apply
(
  select 'empstate', empstate union all
  select 'empstdate', convert(varchar(10), empstdate, 120) union all
  select 'empenddate', convert(varchar(10), empenddate, 120)
) c (col, value);

    Voir Démo. Une fois les données unpivoted, alors vous pouvez appliquer la fonction de PIVOT de sorte que le code final sera:

    select empid,
  empState1, empStDate1, empEndDate1,
  empState2, empStDate2, empEndDate2
from 
(
  select empid, col+cast(empindex as varchar(10)) col,  value
  from sourcetbl
  cross apply
  (
    select 'empstate', empstate union all
    select 'empstdate', convert(varchar(10), empstdate, 120) union all
    select 'empenddate', convert(varchar(10), empenddate, 120)
  ) c (col, value)
) d
pivot
(
  max(value)
  for col in (empState1, empStDate1, empEndDate1,
              empState2, empStDate2, empEndDate2)
) piv;

    Voir SQL jouer avec la Démo.

    Th versions ci-dessus sera excellent travail si vous avez un nombre limité de empindex, mais si pas, alors vous pouvez utiliser le SQL dynamique:

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

select @cols = STUFF((SELECT ',' + QUOTENAME(col+cast(empindex as varchar(10))) 
                    from SourceTbl
                    cross apply
                    (
                      select 'empstate', 1 union all
                      select 'empstdate', 2 union all
                      select 'empenddate', 3
                    ) c (col, so)
                    group by col, so, empindex
                    order by empindex, so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT empid,' + @cols + ' 
            from 
            (
                select empid, col+cast(empindex as varchar(10)) col,  value
                from sourcetbl
                cross apply
                (
                  select ''empstate'', empstate union all
                  select ''empstdate'', convert(varchar(10), empstdate, 120) union all
                  select ''empenddate'', convert(varchar(10), empenddate, 120)
                ) c (col, value)
            ) x
            pivot 
            (
                max(value)
                for col in (' + @cols + ')
            ) p '

execute sp_executesql @query;

    Voir SQL Jouer avec la Démo

    Vous pouvez utiliser ces requêtes afin de les INSÉRER DANS votre DestTbl, ou au lieu de stocker les données dans ce format, vous avez maintenant une requête pour obtenir le résultat souhaité.

    Ces requêtes place les données dans le format:

    | EMPID | EMPSTATE1 | EMPSTDATE1 | EMPENDDATE1 | EMPSTATE2 | EMPSTDATE2 | EMPENDDATE2 |
---------------------------------------------------------------------------------------
|    10 |        AL | 2012-01-01 |  2012-12-01 |        FL | 2012-02-01 |  2013-02-01 |
|    15 |        FL | 2012-03-20 |  2099-01-01 |    (null) |     (null) |      (null) |
    J'apprécie votre effort. Il résoudre mon problème aussi. Merci beaucoup @bluefeet
    J'ai vu POUR XML utilisé beaucoup avant.. mais bravo et thumbsup pour une meilleure utilisation de l': des TRUCS QUOTENAME et NVARCHAR(MAX) dans un PIVOT 🙂 MSACCESS appelle cette TRANSFORMATION (pas sûr de savoir pourquoi la commande n'est jamais arrivé à SQL server)

    OriginalL'auteur Taryn

  2. -1

    Wow c'était plus compliqué que ce que j'imaginais, mais j'ai fait le faire fonctionner à merveille! merci. C'était ma version finale. Le TextKey contient les données que vous souhaitez transformer en colonnes, et la TextValue est la valeur qui se termine à l'intérieur de chaque cellule.

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


select @cols = STUFF((SELECT distinct ', ' + QUOTENAME(TextKey) 
                    from #SourceTbl
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT FromEntityID, DisplayName, ' + @cols + ' 
              FROM 
              (
                  select FromEntityID, DisplayName, TextKey, TextValue
                  from #SourceTbl
              ) x
              pivot 
              (
                  min(TextValue)
                  for TextKey in (' + @cols + ')
              ) p 
              ORDER BY FromEntityID
              '

execute(@query)

    OriginalL'auteur hamish