Maison >base de données >tutoriel mysql >Comment transposer efficacement des colonnes et des lignes en SQL ?
Un moyen simple de convertir des lignes et des colonnes SQL
Bien que la fonction PIVOT de SQL semble adaptée à la conversion colonne-colonne, sa complexité peut être prohibitive. Si vous souhaitez un moyen plus simple d'y parvenir, envisagez les alternatives suivantes :
Utilisez UNION ALL, les fonctions d'agrégation et les instructions CASE
Cette méthode utilise UNION ALL pour développer les données, puis utilise des fonctions d'agrégation et des instructions CASE pour pivoter :
<code class="language-sql">SELECT name, SUM(CASE WHEN color = 'Red' THEN value ELSE 0 END) AS Red, SUM(CASE WHEN color = 'Green' THEN value ELSE 0 END) AS Green, SUM(CASE WHEN color = 'Blue' THEN value ELSE 0 END) AS Blue FROM ( SELECT color, Paul AS value, 'Paul' AS name FROM yourTable UNION ALL SELECT color, John AS value, 'John' AS name FROM yourTable UNION ALL SELECT color, Tim AS value, 'Tim' AS name FROM yourTable UNION ALL SELECT color, Eric AS value, 'Eric' AS name FROM yourTable ) AS src GROUP BY name</code>
Déconstruction statique et perspective
Si vous connaissez la valeur que vous souhaitez convertir, utilisez des valeurs codées en dur pour la déstructuration et le pivotement :
<code class="language-sql">SELECT name, [Red], [Green], [Blue] FROM ( SELECT color, name, value FROM yourTable UNPIVOT ( value FOR name IN (Paul, John, Tim, Eric) ) AS unpiv ) AS src PIVOT ( SUM(value) FOR color IN ([Red], [Green], [Blue]) ) AS piv</code>
Perspective dynamique
Pour un nombre inconnu de colonnes et de couleurs, utilisez du SQL dynamique pour générer des listes déconstruites et pivotées :
<code class="language-sql">DECLARE @colsUnpivot AS NVARCHAR(MAX), @query AS NVARCHAR(MAX), @colsPivot AS NVARCHAR(MAX) SELECT @colsUnpivot = STUFF((SELECT ',' + QUOTENAME(C.name) FROM sys.columns AS C WHERE C.object_id = OBJECT_ID('yourtable') AND C.name 'color' FOR XML PATH('')), 1, 1, '') SELECT @colsPivot = STUFF((SELECT ',' + QUOTENAME(color) FROM yourtable AS t FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') , 1, 1, '') SET @query = 'SELECT name, ' + @colsPivot + ' FROM ( SELECT color, name, value FROM yourtable UNPIVOT ( value FOR name IN (' + @colsUnpivot + ') ) AS unpiv ) AS src PIVOT ( SUM(value) FOR color IN (' + @colsPivot + ') ) AS piv' EXEC(@query)</code>
Les trois méthodes produisent les résultats suivants :
NAME | RED | GREEN | BLUE |
---|---|---|---|
Eric | 3 | 5 | 1 |
John | 5 | 4 | 2 |
Paul | 1 | 8 | 2 |
Tim | 1 | 3 | 9 |
Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!