Comment puis-je simplement transposer des colonnes avec des lignes en SQL ? Existe-t-il une commande simple pour transposer ?
ie transformer ce résultat :
Paul | John | Tim | Eric
Red 1 5 1 3
Green 8 4 3 5
Blue 2 2 9 1
en ceci :
Red | Green | Blue
Paul 1 8 2
John 5 4 2
Tim 1 3 9
Eric 3 5 1
PIVOT
semble trop complexe pour ce scénario.
Il existe plusieurs façons de transformer ces données. Dans votre message initial, vous avez déclaré que la fonction PIVOT
semble trop complexe pour ce scénario, mais elle peut être appliquée très facilement en utilisant les fonctions [UNPIVOT
et PIVOT
][1] dans SQL Server.
Cependant, si vous n'avez pas accès à ces fonctions, vous pouvez reproduire cette opération en utilisant UNION ALL
pour UNPIVOT
, puis une fonction d'agrégation avec une instruction CASE
pour PIVOT
:
Créer une table:
CREATE TABLE yourTable([color] varchar(5), [Paul] int, [John] int, [Tim] int, [Eric] int);
INSERT INTO yourTable
([color], [Paul], [John], [Tim], [Eric])
VALUES
('Red', 1, 5, 1, 3),
('Green', 8, 4, 3, 5),
('Blue', 2, 2, 9, 1);
Tous les syndicats, agrégats et CASE Version:
select name,
sum(case when color = 'Red' then value else 0 end) Red,
sum(case when color = 'Green' then value else 0 end) Green,
sum(case when color = 'Blue' then value else 0 end) Blue
from
(
select color, Paul value, 'Paul' name
from yourTable
union all
select color, John value, 'John' name
from yourTable
union all
select color, Tim value, 'Tim' name
from yourTable
union all
select color, Eric value, 'Eric' name
from yourTable
) src
group by name
Voir [SQL Fiddle with Demo][2]
L'instruction UNION ALL
effectue le UNPIVOT
des données en transformant les colonnes Paul, John, Tim, Eric
en lignes séparées. Ensuite, vous appliquez la fonction d'agrégation sum()
avec l'instruction case
pour obtenir les nouvelles colonnes pour chaque color
.
Version statique de Unpivot et Pivot:
Les fonctions UNPIVOT
et PIVOT
du serveur SQL rendent cette transformation beaucoup plus facile. Si vous connaissez toutes les valeurs que vous voulez transformer, vous pouvez les coder en dur dans une version statique pour obtenir le résultat :
select name, [Red], [Green], [Blue]
from
(
select color, name, value
from yourtable
unpivot
(
value for name in (Paul, John, Tim, Eric)
) unpiv
) src
pivot
(
sum(value)
for color in ([Red], [Green], [Blue])
) piv
Voir [SQL Fiddle with Demo][3].
La requête interne avec le UNPIVOT
exécute la même fonction que le UNION ALL
. Elle prend la liste des colonnes et la transforme en lignes, le PIVOT
effectue ensuite la transformation finale en colonnes.
Version Pivot Dynamique:
Si vous avez un nombre inconnu de colonnes (Paul, John, Tim, Eric
dans votre exemple) et ensuite un nombre inconnu de couleurs à transformer, vous pouvez utiliser dynamic sql pour générer la liste à UNPIVOT
et ensuite PIVOT
:
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 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+')
) unpiv
) src
pivot
(
sum(value)
for color in ('+@colsPivot+')
) piv'
exec(@query)
Voir [SQL Fiddle with Demo][4].
La version dynamique interroge à la fois yourtable
et ensuite la table sys.columns
pour générer la liste des éléments à UNPIVOT
et PIVOT
. Cette liste est ensuite ajoutée à une chaîne de requête à exécuter. L'avantage de la version dynamique est que si vous avez une liste changeante de colors
et/ou de names
, cela générera la liste au moment de l'exécution.
Les trois requêtes produiront le même résultat :
| NAME | RED | GREEN | BLUE |
-----------------------------
| Eric | 3 | 5 | 1 |
| John | 5 | 4 | 2 |
| Paul | 1 | 8 | 2 |
| Tim | 1 | 3 | 9 |
[1] : http://msdn.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx?ppud=4 [2] : http://sqlfiddle.com/#!3/9a05d/1 [3] : http://sqlfiddle.com/#!3/09920/4 [4] : http://sqlfiddle.com/#!3/09920/5
Cela nécessite normalement de connaître au préalable TOUS les libellés des colonnes ET des lignes. Comme vous pouvez le voir dans la requête ci-dessous, les étiquettes sont toutes listées dans leur intégralité dans les opérations UNPIVOT et (re)PIVOT.
MS SQL Server 2012 Schema Setup :
create table tbl (
color varchar(10), Paul int, John int, Tim int, Eric int);
insert tbl select
'Red' ,1 ,5 ,1 ,3 union all select
'Green' ,8 ,4 ,3 ,5 union all select
'Blue' ,2 ,2 ,9 ,1;
Query 1 :
select *
from tbl
unpivot (value for name in ([Paul],[John],[Tim],[Eric])) up
pivot (max(value) for color in ([Red],[Green],[Blue])) p
[Résultats][2] :
| NAME | RED | GREEN | BLUE |
-----------------------------
| Eric | 3 | 5 | 1 |
| John | 5 | 4 | 2 |
| Paul | 1 | 8 | 2 |
| Tim | 1 | 3 | 9 |
Sur la base de cette solution de [bluefeet][2], voici une procédure stockée qui utilise le sql dynamique pour générer la table transposée. Elle exige que tous les champs soient numériques, à l'exception de la colonne transposée (la colonne qui sera l'en-tête de la table résultante) :
/****** Object: StoredProcedure [dbo].[SQLTranspose] Script Date: 11/10/2015 7:08:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Paco Zarate
-- Create date: 2015-11-10
-- Description: SQLTranspose dynamically changes a table to show rows as headers. It needs that all the values are numeric except for the field using for transposing.
-- Parameters: @TableName - Table to transpose
-- @FieldNameTranspose - Column that will be the new headers
-- Usage: exec SQLTranspose <table>, <FieldToTranspose>
-- =============================================
ALTER PROCEDURE [dbo].[SQLTranspose]
-- Add the parameters for the stored procedure here
@TableName NVarchar(MAX) = '',
@FieldNameTranspose NVarchar(MAX) = ''
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @colsUnpivot AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@queryPivot AS NVARCHAR(MAX),
@colsPivot as NVARCHAR(MAX),
@columnToPivot as NVARCHAR(MAX),
@tableToPivot as NVARCHAR(MAX),
@colsResult as xml
select @tableToPivot = @TableName;
select @columnToPivot = @FieldNameTranspose
select @colsUnpivot = stuff((select ','+quotename(C.name)
from sys.columns as C
where C.object_id = object_id(@tableToPivot) and
C.name <> @columnToPivot
for xml path('')), 1, 1, '')
set @queryPivot = 'SELECT @colsResult = (SELECT '',''
+ quotename('+@columnToPivot+')
from '+@tableToPivot+' t
where '+@columnToPivot+' <> ''''
FOR XML PATH(''''), TYPE)'
exec sp_executesql @queryPivot, N'@colsResult xml out', @colsResult out
select @colsPivot = STUFF(@colsResult.value('.', 'NVARCHAR(MAX)'),1,1,'')
set @query
= 'select name, rowid, '+@colsPivot+'
from
(
select '+@columnToPivot+' , name, value, ROW_NUMBER() over (partition by '+@columnToPivot+' order by '+@columnToPivot+') as rowid
from '+@tableToPivot+'
unpivot
(
value for name in ('+@colsUnpivot+')
) unpiv
) src
pivot
(
sum(value)
for '+@columnToPivot+' in ('+@colsPivot+')
) piv
order by rowid'
exec(@query)
END
Vous pouvez le tester avec le tableau fourni avec cette commande :
exec SQLTranspose 'yourTable', 'color'