Un moyen simple de transposer des colonnes et des lignes en SQL ?

Question

Comment échanger simplement des colonnes avec des lignes en SQL ? Existe-t-il une commande simple pour transposer ?

ie tourner ce résultat :

        Paul  | John  | Tim  |  Eric
Red     1       5       1       3
Green   8       4       3       5
Blue    2       2       9       1

dans 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.

Answer 1

En complément de l'excellente réponse de @Paco Zarate, si vous souhaitez transposer un tableau comportant plusieurs types de colonnes, ajoutez ce qui suit à la fin de la ligne 39, de manière à ce que la transposition ne soit effectuée qu'une seule fois int colonnes :

and C.system_type_id = 56   --56 = type int

Voici la requête complète qui est modifiée :

select @colsUnpivot = stuff((select ','+quotename(C.name)
from sys.columns as C
where C.object_id = object_id(@tableToPivot) and
      C.name <> @columnToPivot and C.system_type_id = 56    --56 = type int
for xml path('')), 1, 1, '')

Pour trouver d'autres system_type_id Exécutez ceci :

select name, system_type_id from sys.types order by name

Answer 2

Cette méthode permet de convertir toutes les données des fichiers (colonnes) de la table en enregistrements (rangées).

Declare @TableName  [nvarchar](128)
Declare @ExecStr    nvarchar(max)
Declare @Where      nvarchar(max)
Set @TableName = 'myTableName'
--Enter Filtering If Exists
Set @Where = ''

--Set @ExecStr = N'Select * From '+quotename(@TableName)+@Where
--Exec(@ExecStr)

Drop Table If Exists #tmp_Col2Row

Create Table #tmp_Col2Row
(Field_Name nvarchar(128) Not Null
,Field_Value nvarchar(max) Null
)

Set @ExecStr = N' Insert Into #tmp_Col2Row (Field_Name , Field_Value) '
Select @ExecStr += (Select N'Select '''+C.name+''' ,Convert(nvarchar(max),'+quotename(C.name) + ') From ' + quotename(@TableName)+@Where+Char(10)+' Union All '
         from sys.columns as C
         where (C.object_id = object_id(@TableName)) 
         for xml path(''))
Select @ExecStr = Left(@ExecStr,Len(@ExecStr)-Len(' Union All '))
--Print @ExecStr
Exec (@ExecStr)

Select * From #tmp_Col2Row
Go

Answer 3

J'aimerais partager le code que j'utilise pour transposer un texte divisé basé sur la réponse +bluefeet. Dans cette approche, j'ai implémenté une procédure dans MS. SQL 2005

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      ELD.
-- Create date: May, 5 2016.
-- Description: Transpose from rows to columns the user split function.
-- =============================================
CREATE PROCEDURE TransposeSplit @InputToSplit VARCHAR(8000)
    ,@Delimeter VARCHAR(8000) = ','
AS
BEGIN
    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 = '#tempSplitedTable'

    SELECT @columnToPivot = 'col_number'

    CREATE TABLE #tempSplitedTable (
        col_number INT
        ,col_value VARCHAR(8000)
        )

    INSERT INTO #tempSplitedTable (
        col_number
        ,col_value
        )
    SELECT ROW_NUMBER() OVER (
            ORDER BY (
                    SELECT 100
                    )
            ) AS RowNumber
        ,item
    FROM [DB].[ESCHEME].[fnSplit](@InputToSplit, @Delimeter)

    SELECT @colsUnpivot = STUFF((
                SELECT ',' + quotename(C.NAME)
                FROM [tempdb].sys.columns AS C
                WHERE C.object_id = object_id('tempdb..' + @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
        (
          MAX(value)
          for ' + @columnToPivot + ' in (' + @colsPivot + ')
        ) piv
        order by rowid'

    EXEC (@query)

    DROP TABLE #tempSplitedTable
END
GO

Je mélange cette solution avec l'information sur la façon d'ordonner les lignes sans ordre par ( SQLAuthority.com ) et la fonction split sur MSDN ( social.msdn.microsoft.com )

Lorsque vous exécutez le prodecure

DECLARE @RC int
DECLARE @InputToSplit varchar(MAX)
DECLARE @Delimeter varchar(1)

set @InputToSplit = 'hello|beautiful|world'
set @Delimeter = '|'

EXECUTE @RC = [TransposeSplit] 
   @InputToSplit
  ,@Delimeter
GO

vous obtenez le résultat suivant

  name       rowid  1      2          3
  col_value  1      hello  beautiful  world

Answer 4

J'ai pu utiliser la solution de Paco Zarate et cela fonctionne à merveille. J'ai dû ajouter une ligne ("SET ANSI_WARNINGS ON"), mais c'est peut-être dû à la façon dont je l'ai utilisée ou appelée. Il y a un problème avec mon utilisation et j'espère que quelqu'un pourra m'aider à le résoudre :

La solution ne fonctionne qu'avec une table SQL réelle. J'ai essayé avec une table temporaire et aussi une table en mémoire (déclarée), mais cela ne fonctionne pas. Dans mon code d'appel, je crée donc une table dans ma base de données SQL, puis j'appelle SQLTranspose. Là encore, cela fonctionne parfaitement. C'est exactement ce que je veux. Voici mon problème :

Pour que la solution globale soit réellement dynamique, je dois créer cette table où je stocke temporairement les informations préparées que j'envoie à SQLTranspose "à la volée", puis supprimer cette table une fois que SQLTranspose est appelé. La suppression de la table pose un problème dans mon plan de mise en œuvre final. Le code doit être exécuté à partir d'une application d'utilisateur final (un bouton sur un formulaire/menu Microsoft Access). Lorsque j'utilise ce processus SQL (créer une table SQL, appeler SQLTranspose, supprimer la table SQL), l'application de l'utilisateur final rencontre une erreur parce que le compte SQL utilisé n'a pas les droits nécessaires pour supprimer une table.

Je pense donc qu'il y a plusieurs solutions possibles :

1. Trouvez un moyen de faire fonctionner SQLTranspose avec une table temporaire ou une variable de table déclarée.

2. Trouvez une autre méthode pour la transposition des lignes et des colonnes qui ne nécessite pas une table SQL réelle.

3. Trouver une méthode appropriée pour permettre au compte SQL utilisé par mes utilisateurs finaux de supprimer une table. Il s'agit d'un seul compte SQL partagé codé dans mon application Access. Il semble que cette autorisation soit un privilège de type dbo- qui ne peut être accordé.

Je reconnais que certaines de ces questions peuvent justifier un autre fil de discussion et une autre question. Cependant, comme il est possible qu'une solution soit simplement une façon différente de transposer les lignes et les colonnes, je vais faire mon premier message ici dans ce fil.

EDIT : J'ai aussi remplacé sum(value) par max(value) dans la 6ème ligne à partir de la fin, comme Paco l'a suggéré.

EDIT :

J'ai trouvé quelque chose qui marche pour moi. Je ne sais pas si c'est le meilleur réponse ou non.

J'ai un compte d'utilisateur en lecture seule qui est utilisé pour exécuter des procédures strored et donc générer des rapports à partir d'une base de données. Étant donné que la fonction SQLTranspose que j'ai créée ne fonctionne qu'avec une table "légitime" (pas une table déclarée ni une table temporaire), j'ai dû trouver un moyen pour qu'un compte utilisateur en lecture seule puisse créer (puis supprimer) une table.

Je me suis dit que pour mes besoins, il n'y avait pas de problème à ce que le compte utilisateur soit autorisé à créer une table. Cependant, l'utilisateur ne pouvait toujours pas supprimer la table. Ma solution a été de créer un schéma où le compte utilisateur est autorisé. Ensuite, chaque fois que je crée, utilise ou supprime cette table, je la réfère au schéma spécifié.

J'ai d'abord lancé cette commande depuis un compte 'sa' ou 'sysadmin' : CREATE SCHEMA ro AUTHORIZATION

Chaque fois que je fais référence à ma table "tmpoutput", je la spécifie comme cet exemple :

drop table ro.tmpoutput