SQL Server: kolumny do wierszy


128

Szukasz eleganckiego (lub dowolnego) rozwiązania do konwersji kolumn na wiersze.

Oto przykład: Mam tabelę z następującym schematem:

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

Oto, co chcę uzyskać jako wynik:

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

A wartościami wyniku będą:

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'

I tak dalej..

Czy to ma sens? Czy masz jakieś sugestie, gdzie szukać i jak to zrobić w T-SQL?


2
Czy spojrzałeś już na Pivot / Unpivot ?
Josh Jay

Na koniec poszło z rozwiązaniem bluefeeta. Eleganckie i funkcjonalne. Wielkie dzięki wszystkim.
Siergiej

Odpowiedzi:


247

Możesz użyć funkcji UNPIVOT, aby przekonwertować kolumny na wiersze:

select id, entityId,
  indicatorname,
  indicatorvalue
from yourtable
unpivot
(
  indicatorvalue
  for indicatorname in (Indicator1, Indicator2, Indicator3)
) unpiv;

Zauważ, że typy danych kolumn, których nie obracasz, muszą być takie same, więc może być konieczne przekonwertowanie typów danych przed zastosowaniem unpivot.

Możesz również użyć CROSS APPLYUNION ALL, aby przekonwertować kolumny:

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);

W zależności od wersji programu SQL Server możesz nawet użyć CROSS APPLY z klauzulą ​​VALUES:

select id, entityid,
  indicatorname,
  indicatorvalue
from yourtable
cross apply
(
  values
  ('Indicator1', Indicator1),
  ('Indicator2', Indicator2),
  ('Indicator3', Indicator3),
  ('Indicator4', Indicator4)
) c (indicatorname, indicatorvalue);

Wreszcie, jeśli masz 150 kolumn do cofnięcia przestawienia i nie chcesz na stałe zakodować całego zapytania, możesz wygenerować instrukcję sql za pomocą dynamicznego SQL:

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;

4
Dla tych, którzy chcą więcej nakrętek i śrub UNPIVOTi / vs. APPLY, ten post na blogu z 2010 roku autorstwa Brada Schulza (i jego następcy ) jest (są) piękny.
ruffin

2
Msg 8167, poziom 16, stan 1, wiersz 147 Typ kolumny „blahblah” jest w konflikcie z typem innych kolumn określonych na liście UNPIVOT.
JDPeckham

@JDPeckham Jeśli masz różne typy danych, przed wykonaniem unpivot musisz je przekonwertować, aby były tego samego typu i długości. Tutaj jest więcej informacji na ten temat .
Taryn

metoda xml ma wadę, ponieważ nie udaje się jej usunąć z kodu XML, takiego jak & gt ;, & lt; i & amp ;. Ponadto wydajność można znacznie poprawić, przepisując w następujący sposób: select @colsUnpivot = stuff ((select ',' + quotename (C.column_name) as [text ()] z information_schema.columns as C, gdzie C.table_name = 'yourtable' ' i C.nazwa_kolumny jak „Indicator%” dla ścieżki xml („”), typ) .value („text () [1]”, „nvarchar (max)”), 1, 1, '')
rrozema

24

cóż, jeśli masz 150 kolumn, myślę, że UNPIVOT nie wchodzi w grę. Więc możesz użyć sztuczki XML

;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 fiddle demo

Mógłbyś też napisać dynamiczny SQL, ale ja bardziej lubię xml - dla dynamicznego SQL musisz mieć uprawnienia do wybierania danych bezpośrednio z tabeli, a to nie zawsze jest możliwe.

AKTUALIZACJA
Ponieważ komentarze są bardzo popularne, myślę, że dodam kilka zalet i wad XML / Dynamic SQL. Postaram się być jak najbardziej obiektywny i nie wspominać o elegancji i brzydocie. Jeśli masz inne za i przeciw, edytuj odpowiedź lub napisz w komentarzach

Cons

  • nie jest tak szybki jak dynamiczny SQL, zgrubne testy dały mi, że xml jest około 2,5 razy wolniejszy od dynamicznego (było to jedno zapytanie na ~ 250000 wierszy tabeli, więc to oszacowanie nie jest dokładne). Możesz to porównać samodzielnie, jeśli chcesz, oto przykład sqlfiddle , dla 100000 wierszy było to 29s (xml) vs 14s (dynamiczne);
  • może być trudniejsze do zrozumienia dla osób niezaznajomionych z xpath;

plusy

  • ma taki sam zakres jak inne zapytania i może być bardzo przydatne. Przychodzi mi na myśl kilka przykładów
    • możesz zapytać insertedi deletedtabele wewnątrz wyzwalacza (nie jest to w ogóle możliwe w przypadku dynamiki);
    • użytkownik nie musi mieć uprawnień do bezpośredniego wyboru z tabeli. Chodzi mi o to, że jeśli masz warstwę procedur składowanych i użytkownik ma uprawnienia do uruchamiania sp, ale nie masz uprawnień do bezpośredniego wykonywania zapytań do tabel, nadal możesz użyć tego zapytania wewnątrz procedury składowanej;
    • możesz zapytać o zmienną tabeli, którą umieściłeś w swoim zasięgu (aby przekazać ją wewnątrz dynamicznego SQL, musisz albo uczynić ją tymczasową tabelą, albo utworzyć typ i przekazać go jako parametr do dynamicznego SQL;
  • możesz wykonać to zapytanie wewnątrz funkcji (skalarne lub wartościowane w tabeli). Nie jest możliwe użycie dynamicznego SQL wewnątrz funkcji;

2
Jakie dane wybierasz za pomocą XML, które nie wymagają wybierania danych z tabeli?
Aaron Bertrand

1
Na przykład możesz zdecydować, aby nie dawać użytkownikom uprawnień do wybierania danych z tabel, ale tylko do procedur składowanych pracujących z tabelami, więc mógłbym wybrać XML w procedurze, ale muszę zastosować pewne obejścia, jeśli chcę używać dynamicznego SQL
Roman Pekar

3
Jeśli chcesz, aby Twoi użytkownicy mogli wykonywać kod, musisz w pewnym sensie zapewnić im dostęp, jakiego potrzebują, aby wykonać kod. Nie twórz wymagań, które nie istnieją, aby twoja odpowiedź brzmiała lepiej (nie musisz też komentować konkurencyjnych odpowiedzi, aby spojrzeć na swoją odpowiedź - jeśli znaleźli tę odpowiedź, mogą również znaleźć twoją).
Aaron Bertrand

2
Również jeśli uzasadnieniem dla używania XML jest to, że możesz umieścić go w procedurze składowanej, aby uniknąć bezpośredniego dostępu do tabeli, być może Twój przykład powinien pokazać, jak umieścić go w procedurze składowanej i jak nadać uprawnienia użytkownikowi, aby może go wykonać bez dostępu do odczytu do tabeli bazowej. Dla mnie to pełzanie zakresu, ponieważ większość ludzi piszących zapytania na podstawie tabeli ma dostęp do odczytu z tabeli.
Aaron Bertrand

2
Powiedziałbym, że 10-krotna różnica w czasie trwania ma znaczenie, tak. A ~ 8 000 wierszy to nie „duże ilości danych” - czy powinniśmy zobaczyć, co się stanie z 800 000 wierszami?
Aaron Bertrand

7

Aby pomóc nowym czytelnikom, stworzyłem przykład, aby lepiej zrozumieć odpowiedź @ bluefeet na temat 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;

3

Potrzebowałem rozwiązania do konwersji kolumn na wiersze w Microsoft SQL Server, bez znajomości nazw kolumn (używanych w wyzwalaczu) i bez dynamicznego sql (dynamiczny sql jest zbyt wolny do użycia w wyzwalaczu).

W końcu znalazłem to rozwiązanie, które działa dobrze:

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)

Jak widać, konwertuję wiersz na XML (Subquery select i, * dla xml raw, to konwertuje wszystkie kolumny na jedną kolumnę xml)

Następnie CROSS STOSUJĘ funkcję do każdego atrybutu XML tej kolumny, tak aby uzyskać jeden wiersz na atrybut.

Ogólnie rzecz biorąc, konwertuje to kolumny na wiersze bez znajomości nazw kolumn i bez używania dynamicznego sql. Jest wystarczająco szybki jak na mój cel.

(Edycja: właśnie zobaczyłem powyżej odpowiedź Romana Pekara, który robi to samo. Najpierw użyłem dynamicznego wyzwalacza sql z kursorami, który był 10 do 100 razy wolniejszy niż to rozwiązanie, ale może było to spowodowane przez kursor, a nie przez dynamiczny sql. Zresztą to rozwiązanie jest bardzo proste i uniwersalne, więc definitywnie jest opcją).

Ten komentarz zostawiam w tym miejscu, bo do tego wyjaśnienia chcę się odwołać w moim poście o pełnej wyzwalaczu audytu, który znajdziesz tutaj: https://stackoverflow.com/a/43800286/4160788


3
DECLARE @TableName varchar(max)=NULL
SELECT @TableName=COALESCE(@TableName+',','')+t.TABLE_CATALOG+'.'+ t.TABLE_SCHEMA+'.'+o.Name
  FROM sysindexes AS i
  INNER JOIN sysobjects AS o ON i.id = o.id
  INNER JOIN INFORMATION_SCHEMA.TABLES T ON T.TABLE_NAME=o.name
 WHERE i.indid < 2
  AND OBJECTPROPERTY(o.id,'IsMSShipped') = 0
  AND i.rowcnt >350
  AND o.xtype !='TF'
 ORDER BY o.name ASC

 print @tablename

Możesz uzyskać listę tabel, które mają liczbę wierszy> 350. Możesz zobaczyć na liście rozwiązań tabela jako wiersz.


2

Tylko dlatego, że o tym nie wspomniałem.

Jeśli wersja 2016+, oto kolejna opcja dynamicznego unpivot danych bez faktycznego korzystania z Dynamic SQL.

Przykład

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

Zwroty

ID  Item    Value
1   Col1    A
1   Col2    B
2   Col1    R
2   Col2    C
3   Col1    X
3   Col2    D
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.