SQL Server : les Colonnes de Lignes

La recherche d'élégant (ou toutes les) solution pour convertir les colonnes de lignes.

Voici un exemple: j'ai une table avec le schéma suivant:

[ID] [EntityID] [Indicator1] [Indicator2] [Indicator3] ... [Indicator150]

Voici ce que je veux obtenir comme résultat:

[ID] [EntityId] [IndicatorName] [IndicatorValue]

Et les valeurs de résultat sera:

1 1 'Indicator1' 'Value of Indicator 1 for entity 1'
2 1 'Indicator2' 'Value of Indicator 2 for entity 1'
3 1 'Indicator3' 'Value of Indicator 3 for entity 1'
4 2 'Indicator1' 'Value of Indicator 1 for entity 2'

Et ainsi de suite..

Cela fait-il sens? Avez-vous des suggestions sur où chercher et comment s'en sortir en T-SQL?

Avez-vous regardé dans les Pivot/Unpivot encore?
À la fin de qu'il est allé avec le bluefeet de la solution. Élégant et fonctionnel. Merci beaucoup tout le monde.

InformationsquelleAutor Sergei | 2013-08-02

206

Vous pouvez utiliser le UNPIVOT fonction pour convertir les colonnes en lignes:
```
select id, entityId,
  indicatorname,
  indicatorvalue
from yourtable
unpivot
(
  indicatorvalue
  for indicatorname in (Indicator1, Indicator2, Indicator3)
) unpiv;
```
Remarque, les types de données des colonnes vous sont unpivoting doivent être les mêmes, de sorte que vous pouvez convertir les types de données avant l'application de la unpivot.

Vous pouvez également utiliser CROSS APPLY avec l'UNION de TOUS pour convertir les colonnes:
```
select id, entityid,
  indicatorname,
  indicatorvalue
from yourtable
cross apply
(
  select 'Indicator1', Indicator1 union all
  select 'Indicator2', Indicator2 union all
  select 'Indicator3', Indicator3 union all
  select 'Indicator4', Indicator4 
) c (indicatorname, indicatorvalue);
```
Selon votre version de SQL Server, vous pouvez même utiliser de la CROIX s'APPLIQUENT, avec les VALEURS de la clause:
```
select id, entityid,
  indicatorname,
  indicatorvalue
from yourtable
cross apply
(
  values
  ('Indicator1', Indicator1),
  ('Indicator2', Indicator2),
  ('Indicator3', Indicator3),
  ('Indicator4', Indicator4)
) c (indicatorname, indicatorvalue);
```
Enfin, si vous avez 150 colonnes à unpivot et vous ne voulez pas coder en dur l'intégralité de la requête, puis vous pouvez générer l'instruction sql à l'aide de SQL dynamique:
```
DECLARE @colsUnpivot AS NVARCHAR(MAX),
   @query  AS NVARCHAR(MAX)

select @colsUnpivot 
  = stuff((select ','+quotename(C.column_name)
           from information_schema.columns as C
           where C.table_name = 'yourtable' and
                 C.column_name like 'Indicator%'
           for xml path('')), 1, 1, '')

set @query 
  = 'select id, entityId,
        indicatorname,
        indicatorvalue
     from yourtable
     unpivot
     (
        indicatorvalue
        for indicatorname in ('+ @colsunpivot +')
     ) u'

exec sp_executesql @query;
```
- Pour ceux qui veulent plus d'écrous et de boulons sur UNPIVOT et/vs APPLY, ce 2010 blog de Brad Schulz (et la suite) est (sont) la belle.
- Msg 8167, Niveau 16, État 1, Ligne 147 Le type de la colonne "blahblah" les conflits avec l'autre type de colonnes spécifiées dans la UNPIVOT liste.
- Si vous avez différents types de données, alors vous avez besoin de les convertir à être du même type et de la longueur avant de procéder à la unpivot. Voici plus d'informations à ce sujet.
- le xml méthode a un défaut, car il ne parvient pas à ne pas encoder xml codes >, < et &. En Plus de la performance peut être améliorée de manière significative par la réécriture comme suit: select @colsUnpivot = stuff((select ','+quotename(C. column_name) comme [text()] from information_schema.les colonnes C où C. table_name = 'yourtable' et C. column_name like '% Indicateur " pour xml path("), type).valeur('text()[1]','type nvarchar(max)'), 1, 1, ")
InformationsquelleAutor Taryn
19

eh bien, Si vous avez 150 colonnes, alors je pense que UNPIVOT n'est pas une option. Vous pouvez utiliser xml truc
```
;with CTE1 as (
    select ID, EntityID, (select t.* for xml raw('row'), type) as Data
    from temp1 as t
), CTE2 as (
    select
         C.id, C.EntityID,
         F.C.value('local-name(.)', 'nvarchar(128)') as IndicatorName,
         F.C.value('.', 'nvarchar(max)') as IndicatorValue
    from CTE1 as c
        outer apply c.Data.nodes('row/@*') as F(C)
)
select * from CTE2 where IndicatorName like 'Indicator%'
```
sql violon démo

Vous pouvez également écrire du SQL dynamique, mais j'aime xml plus - pour le SQL dynamique, vous devez avoir les autorisations pour sélectionner les données directement à partir de la table et qui n'est pas toujours une option.

Mise à JOUR
Comme il y a une grande flamme dans les commentaires, je pense que je vais ajouter quelques avantages et inconvénients de xml/SQL dynamique. Je vais essayer d'être aussi objectif que je pouvais et ne pas mentionner elegantness et uglyness. Si vous avez d'autres avantages et les inconvénients, de modifier la réponse ou de l'écrire dans les commentaires

contre
- c'est pas aussi vite comme SQL dynamique, rugueuse des tests m'a donné que xml est environ 2,5 fois plus lent que dynamique (il était une requête sur ~250000 lignes de la table, de sorte que cette estimation n'est pas une façon exacte). Vous pouvez comparer vous-même si vous le souhaitez, voici sqlfiddle exemple, sur 100000 lignes, il a été 29s (xml) vs 14s (dynamique);
- peut-être il pourrait être plus difficile à comprendre pour les personnes qui ne connaissent pas xpath;
pros
- c'est le même portée que vos autres requêtes, et qui pourrait s'avérer très utile. Quelques exemples viennent à l'esprit
  - vous pouvez lancer une requête inserted et deleted tables à l'intérieur de votre déclencher (pas possible avec la dynamique de tous);
  - utilisateur n'avez pas à avoir autorisations sur la sélection directe à partir de la table. Ce que je veux dire est que si vous avez des procédures stockées de la couche et l'utilisateur ont des autorisations pour exécuter la sp, mais ne disposez pas des autorisations pour des tables de requêtes directement, vous pourriez utiliser cette requête à l'intérieur d'une procédure stockée;
  - vous pourriez table de requête variable vous avez renseignée dans votre périmètre (à transmettre à l'intérieur de la dynamique SQL, vous devez soit faire table temporaire à la place ou en créer de type et de le passer comme paramètre dans le SQL dynamique;
- vous pouvez le faire requête à l'intérieur de la fonction (scalaire ou table). Il n'est pas possible d'utiliser le SQL dynamique à l'intérieur de l'fonctions;
- Quelles sont les données que vous en sélectionnant avec XML qui n'exige pas la sélection des données de la table?
- Par exemple, vous pourriez décider de ne pas donner des autorisations aux utilisateurs de sélectionner des données à partir de tables, mais seulement sur des procédures stockées de travailler avec des tables, afin que je puisse sélectionner pour xml à l'intérieur de la procédure, mais j'ai utiliser quelques solutions de contournement si je veux utiliser le SQL dynamique
- Si vous souhaitez que vos utilisateurs soient en mesure d'exécuter le code, vous sorte de leur donner quoi que l'accès dont ils ont besoin pour exécuter le code. Ne faites pas aux exigences qui n'existent pas pour faire de votre réponse sonne mieux (vous pouvez aussi ne pas avoir de commentaires sur les réponses à regarder votre réponse - si ils ont trouvé la réponse, ils peuvent trouver le vôtre aussi).
- Je pourrais leur donner la permission de procédure stockée et si la procédure stockée de travailler avec des tables de même db, les utilisateurs n'ont pas besoin d'autorisations pour sélectionner à partir de la table, le savez-vous? Est-il étrange à la recherche pour vous de ne pas donner tous les droits aux utilisateurs de sélectionner toutes les données qu'ils veulent de la table? Par exemple, j'ai quelques horizontale de l'autorisation du système, donc je ne veux pas que les utilisateurs soient en mesure de récupérer toutes les lignes
- Saviez-vous que vous pouvez utiliser une vue d'exposer seulement certaines colonnes (ou lignes!) pour les utilisateurs, et ils pourraient écrire de façon plus simple requête à l'encontre de ce point de vue?
- c'est vrai système dont je vous ai parlé, ne pense pas que j'ai besoin de faire quelque chose à faire de ma réponse sonne mieux
- Mon point est qu'il n'est pas actuellement connue pour être l'un des OP exigences, il est donc difficile de l'utiliser pour justifier votre réponse, c'est mieux.
- oui je le fais, et parfois j'ai besoin de passer des paramètres à la procédure de vue pas toujours une option.
- ai-je dit quelque part que ma réponse est le meilleur? Je pense que c'est la solution élégante et j'ai dit que je l'aime plus.
- Hein? Votre procédure stockée peut accepter des paramètres, vous pouvez les utiliser à l'encontre de la vue. Vous pouvez également créer une fonction table qui agit comme une vue paramétrée. Vous n'avez pas à résoudre les problèmes de sécurité par l'écriture obscure XML.
- Si vous l'aimez plus que les autres répondre, mais ce n'est pas mieux? Avez-vous une idée de la façon de fonctionner de la communauté? soupir
- J'aime de plus est suubjective, meilleure est l'objectif
- Aussi, si votre justification pour l'utilisation de XML est que vous pouvez les mettre dans une procédure stockée pour éviter de donner un accès direct à la table, peut-être que votre exemple devrait montrer comment le mettre dans une procédure stockée et l'octroi de droits à un utilisateur, de sorte qu'ils peuvent l'exécuter sans avoir un accès en lecture à la table sous-jacente. Pour moi c'est la portée fluage, parce que la plupart des gens de l'écriture de requêtes à l'encontre d'un tableau ont un accès en lecture à la table.
- Et s'il vous plaît être conscient que lorsque vous répondez à des questions que d'autres gens qui sont censés apprendre, les avis que vous avez de l'état sont toujours à son plus objectif et les faits que vous le pensez. "J'aime mieux" est souvent va se traduire par "C'est mieux".
- pour moi, le sql dynamique ressemble plus laid que cette solution xml, il s'agit essentiellement de sélection simple, je peux mettre les données dans la table (sans utiliser exec ou de la table partagée). J'ai pensé que je tiens à souligner l'une des raisons que j'ai rencontré dans ma expirience pourquoi quelqu'un pourrait-il préfère de cette façon. L'OP est libre de choisir la solution qu'il veut. J'ai pensé à mettre un peu les autres, mais alors cette "discussion" surviennent
- Je ne sais pas pour vous, mais pour moi cette solution est très élégant, un seleect - et vous avez EAV de données 🙂
- Oui, maintenant vous expliquer à un débutant total comment il fonctionne, et de voir comment ils peuvent adapter cette requête à un scénario différent.
- Vous pouvez ajouter la performance quelque part en haut de votre liste de priorité que les exigences en matière de sécurité qui n'existent même pas. Vous dites que le SQL dynamique est plus laid que le XML; je dis que vilain est moins importante dans le code si cela fonctionne comme ceci.
- oui, il pourrait être plus lent sur de grandes quantités de données, mais pour moi, c'généraliser mieux que du SQL dynamique et parfois des questions trop. Je veux dire, C++ est plus rapide que python, mais parfois, vous avez à écrire généralisables code 🙂
- Je dirais un 10x différence dans la durée est importante, oui. Et ~de 8 000 lignes n'est pas de "grandes quantités de données" - faut-il voir ce qui se passe à l'encontre de 800 000 lignes?
- eh bien, je ne sais pas comment l'avez-vous testé, il n'est pas toujours sur le plan, mais à propos d'exécution réel. Je l'ai essayé sur ~250000 lignes: ma requête d'environ 2,5 x (pas 10) fois plus lent sur ma tablette (20 vs 49s). Mais ensuite, j'ai essayé d'insérer des données dans une table temporaire (plaine d'insertion pour ma requête, insérez dans exec pour sql dynamique, et ma requête a travaillé pour ~35s tout insérer dans exec travaillé environ 1,5 min, puis j'ai arrêté. Honnêtement, quand j'ai déménagé à insérer dans la table temporaire en dynamique, il a travaillé pour ~15s. Si vous ne le faites pas tout le temps pour un grand nombre de lignes, je pense qu'il n'a pas beaucoup d'importance
- si ce n'est pour la perf. les tâches critiques et je doute que l'on est
- Je ne suis pas sûr de savoir pourquoi vous obtenez à supposer que cela le problème de l'utilisateur n'est pas "perf. critique de" mais aussi, et surtout, n'oubliez pas que cette réponse sera lue par le de nombreux autres utilisateurs dans le futur, trop. Donc, en supposant qu'aucun de leurs problèmes seront "perf. critique" soit est assez dangereuse. Je suis content que vous avez reconnu les problèmes de performances de la poste (même si vous avez minimisé), mais je pense qu'ils sont encore plus importantes que vous êtes à l'admettre.
- Cette réponse m'a vraiment aidé, merci!
- Qu'est-F(C) faire réellement?
- Son aliasing, le jeu d'enregistrements - F est un alias de table et de C est un alias de colonne
InformationsquelleAutor Roman Pekar

Juste pour aider les nouveaux lecteurs, j'ai créé un exemple pour mieux comprendre @bluefeet réponse sur UNPIVOT.

 SELECT id
        ,entityId
        ,indicatorname
        ,indicatorvalue
  FROM (VALUES
        (1, 1, 'Value of Indicator 1 for entity 1', 'Value of Indicator 2 for entity 1', 'Value of Indicator 3 for entity 1'),
        (2, 1, 'Value of Indicator 1 for entity 2', 'Value of Indicator 2 for entity 2', 'Value of Indicator 3 for entity 2'),
        (3, 1, 'Value of Indicator 1 for entity 3', 'Value of Indicator 2 for entity 3', 'Value of Indicator 3 for entity 3'),
        (4, 2, 'Value of Indicator 1 for entity 4', 'Value of Indicator 2 for entity 4', 'Value of Indicator 3 for entity 4')
       ) AS Category(ID, EntityId, Indicator1, Indicator2, Indicator3)
UNPIVOT
(
    indicatorvalue
    FOR indicatorname IN (Indicator1, Indicator2, Indicator3)
) UNPIV;

InformationsquelleAutor Dmyan

DECLARE @TableName nvarchar(50)
DECLARE column_to_row CURSOR FOR

--List of tables that we want to unpivot columns as row
SELECT DISTINCT t.name FROM sys.tables t
JOIN sys.schemas s ON t.schema_id=t.schema_id
WHERE t.name like '%_CT%'
AND s.name='cdc'

OPEN  column_to_row
FETCH NEXT FROM column_to_row INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @script nvarchar(max) = null
DECLARE @columns nvarchar(2000) = null

-- keep the table's column list
select @columns = COALESCE(@columns + ',','') + c.name from sys.tables  t
join sys.columns c on t.object_id = c.object_id
where t.name = @TableName

set @script = 'SELECT '+@columns+' FROM [cdc].['+@TableName+'] (nolock)'
--print (@script)
exec (@script)

FETCH NEXT FROM column_to_row INTO @TableName
END
CLOSE column_to_row
DEALLOCATE column_to_row

Voici une autre méthode pour les colonnes de lignes, combien de table et le nombre de colonnes que vous avez, n'est pas important. Venez de définir les paramètres, et obtenir le résultat.
J'ai écrit cela, parce que parfois j'ai besoin de la suite de la table (qui est la colonne ensemble de résultats), en tant que champs d'une autre table B (qui a à être des champs de ligne). Dans ce cas, je ne sais pas combien de champs, j'ai mis pour ma table B.

InformationsquelleAutor cunay

3

J'ai besoin d'une solution pour convertir les colonnes de lignes dans Microsoft SQL Server, sans connaître la colonne (les noms utilisés dans le déclencheur) et sans sql dynamique (dynamic sql est trop lent pour une utilisation dans un déclencheur).

J'ai enfin trouvé cette solution, qui fonctionne très bien:
```
SELECT
    insRowTbl.PK,
    insRowTbl.Username,
    attr.insRow.value('local-name(.)', 'nvarchar(128)') as FieldName,
    attr.insRow.value('.', 'nvarchar(max)') as FieldValue 
FROM ( Select      
          i.ID as PK,
          i.LastModifiedBy as Username,
          convert(xml, (select i.* for xml raw)) as insRowCol
       FROM inserted as i
     ) as insRowTbl
CROSS APPLY insRowTbl.insRowCol.nodes('/row/@*') as attr(insRow)
```
Comme vous pouvez le voir, j'convertir la ligne en XML (sous-Requête select i,* pour xml brut, ce convertit toutes les colonnes dans une colonne xml)

Puis je CROIX APPLIQUER une fonction à chaque attribut XML de cette colonne, de sorte que je reçois une ligne par attribut.

Dans l'ensemble, cette convertit les colonnes en lignes, sans connaître les noms de colonne et sans l'aide de sql dynamique. Il est assez rapide pour mon but.

(Edit: je viens de voir Romain Pekar réponse ci-dessus, qui est de faire de même.
J'ai utilisé le sql dynamique de déclenchement avec les curseurs de la première, qui était de 10 à 100 fois plus lent que cette solution, mais peut-être qu'il a été causé par le curseur, pas par le sql dynamique. De toute façon, cette solution est très simple et universel, de sorte que son définitivement une option).

Je laisse ce commentaire à cet endroit, parce que je veux faire référence à cette explication dans mon post à propos de la vérification complète de déclenchement, que vous pouvez retrouver ici: https://stackoverflow.com/a/43800286/4160788

InformationsquelleAutor flack

Juste parce que je n'ai pas vu mentionné.

Si 2016+, voici encore une autre option pour dynamiquement unpivot de données sans l'aide de SQL Dynamique.

Exemple

Declare @YourTable Table ([ID] varchar(50),[Col1] varchar(50),[Col2] varchar(50))
Insert Into @YourTable Values 
 (1,'A','B')
,(2,'R','C')
,(3,'X','D')

Select A.[ID]
      ,Item  = B.[Key]
      ,Value = B.[Value]
 From  @YourTable A
 Cross Apply ( Select * 
                From  OpenJson((Select A.* For JSON Path,Without_Array_Wrapper )) 
                Where [Key] not in ('ID','Other','Columns','ToExclude')
             ) B

Retourne

ID  Item    Value
1   Col1    A
1   Col2    B
2   Col1    R
2   Col2    C
3   Col1    X
3   Col2    D

InformationsquelleAutor John Cappelletti

Vous devez vous connecter pour publier un commentaire.