MS SQL Server
  1. MS SQL Server
  3. Conversion de plusieurs lignes en colonnes avec un en-tête spécifié
Intereting Posts
Configuration de SQL Server 2014 Express Comment récupérer la valeur d'une autre ligne dans la même pipleline de données? Soustrayez le même nombre pour toutes les lignes SQL de la table devise d'administration de site Web ASP.NET: Impossible de se connecter à la database SQL Server Comment find les requêtes SQL Server qui ont pris beaucoup de time? Récupération de la valeur max SQL Server Clés primaires / étrangères de SQL Server Est-il possible de modifier un LEFT JOIN pour returnner une ligne nulle où il y a une correspondance ainsi que la correspondance? La fonction SQL ne renvoie aucun résultat, mais le contenu de la fonction renvoie les résultats Plan d'exécution sur requête avec tables temporaires SQL Server ne trouve pas la string avec des parenthèses Dans un file SQL XDL, comment lire l'atsortingbut waitresource sur les noeuds de process qui se bloquent? Quelle est la string de connection pour localdb pour la version 11 Pad un nombre avec des zéros, mais seulement si elle est en dessous d'une certaine longueur? Performances d'insertion d'index en cluster sur une colonne pas si unique?

Conversion de plusieurs lignes en colonnes avec un en-tête spécifié

J'ai un tableau qui contient les détails des activités menées par les individus – le contenu de ce tableau est similaire à celui-ci: 

 |  Personne |  Catégorie |  Activité |
 --------------------------------------
 |  Nom d'user1 |  X |  X1 |
 |  Nom d'user1 |  Y |  Y1 |
 |  Nom d'user1 |  Z |  Z1 |

J'ai besoin d'une requête SQL qui peut produire quelque chose comme ce qui suit et toute aide serait appréciée: 

 |  Personne |  Cat1 |  Cat1_Act | Cat2 |  Cat2_Act |  Cat3 |  Cat3_Act |
 -------------------------------------------------- -------------
 |  Nom d'user1 |  X |  X1 |  Y |  Y1 |  Z |  Z1 |

J'ai lu à travers un certain nombre de messages que PIVOT peut être utilisé pour y parvenir mais je n'ai pas trouvé de solution proche de ce dont j'ai besoin car la plupart des solutions utilisent souvent des valeurs telles que 'X', 'Y', 'Z' (dans ma table d'exemple) comme en-têtes de table mais je veux idéalement être en mesure de spécifier le nom des en-têtes de table contenant les nouvelles colonnes (J'espère que tout cela a du sens et que quelqu'un peut aider :-))

Il y a plusieurs façons d'get le résultat souhaité. Si vous avez un nombre limité de valeurs que vous voulez PIVOT dans des colonnes, vous pouvez coder en dur la requête de différentes manières.

Fonction d'agrégation avec CASE : 

select person, max(case when seq = 1 then category end) Cat1, max(case when seq = 1 then activity end) Cat1_Act, max(case when seq = 2 then category end) Cat2, max(case when seq = 2 then activity end) Cat2_Act, max(case when seq = 3 then category end) Cat3, max(case when seq = 3 then activity end) Cat3_Act from ( select person, category, activity, row_number() over(partition by person order by category) seq from yourtable ) d group by person;

Voir SQL Fiddle avec démo . En atsortingbuant une séquence ou un numéro de ligne à chaque catégorie par user, vous pouvez utiliser ce numéro de ligne pour convertir les lignes en colonnes.

PIVOT statique:

Si vous souhaitez appliquer la fonction PIVOT, je suggère d'abord de ne pas faire pivoter les colonnes de category et d' activity en plusieurs lignes, puis d'appliquer la fonction de pivot. 

 ;with cte as ( select person, category, activity, row_number() over(partition by person order by category) seq from yourtable ) select person, cat1, cat1_act, cat2, cat2_act, cat3, cat3_act from ( select t.person, col = case when c.col = 'cat' then col+cast(seq as varchar(10)) else 'cat'+cast(seq as varchar(10))+'_'+col end, value from cte t cross apply ( select 'cat', category union all select 'act', activity ) c (col, value) ) d pivot ( max(value) for col in (cat1, cat1_act, cat2, cat2_act, cat3, cat3_act) ) piv;

Voir SQL Fiddle avec démo

PIVOT dynamic: Enfin, si vous avez un nombre inconnu de valeurs, vous pouvez utiliser le SQL dynamic pour get le résultat: 

 DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT ',' + QUOTENAME(case when d.col = 'cat' then col+cast(seq as varchar(10)) else 'cat'+cast(seq as varchar(10))+'_'+col end) from ( select row_number() over(partition by person order by category) seq from yourtable ) t cross apply ( select 'cat', 1 union all select 'act', 2 ) d (col, so) group by col, so, seq order by seq, so FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT person, ' + @cols + ' from ( select t.person, col = case when c.col = ''cat'' then col+cast(seq as varchar(10)) else ''cat''+cast(seq as varchar(10))+''_''+col end, value from ( select person, category, activity, row_number() over(partition by person order by category) seq from yourtable ) t cross apply ( select ''cat'', category union all select ''act'', activity ) c (col, value) ) x pivot ( max(value) for col in (' + @cols + ') ) p ' execute sp_executesql @query;

Voir SQL Fiddle avec démo . Toutes les versions donnent un résultat: 

 | PERSON | CAT1 | CAT1_ACT | CAT2 | CAT2_ACT | CAT3 | CAT3_ACT | | Username1 | X | X1 | Y | Y1 | Z | Z1 |

c'est un exemple simple 

  SELECT Person, MAX(CASE Category WHEN 'X' THEN Activity ELSE 0 END) AS 'X' MAX(CASE Category WHEN 'Y' THEN Activity ELSE 0 END) AS 'Y' MAX(CASE Category WHEN 'Z' THEN Activity ELSE 0 END) AS 'Z' FROM mytable GROUP BY Person