SQL-Server : dynamische pivot-über 5 Spalten

Ich habe eine sehr harte Zeit versucht, herauszufinden, wie zu tun, eine dynamische pivot in SQL Server 2008 mit mehreren Spalten.

Meine Beispiel-Tabelle wie folgt:

ID  YEAR  TYPE  TOTAL   VOLUME
DD1 2008    A   1000    10
DD1 2008    B   2000    20
DD1 2008    C   3000    30
DD1 2009    A   4000    40
DD1 2009    B   5000    50
DD1 2009    C   6000    60
DD2 2008    A   7000    70
DD2 2008    B   8000    80
DD2 2008    C   9000    90
DD2 2009    A   10000   100
DD2 2009    B   11000   110
DD2 2009    C   12000   120

und ich versuche, den Drehpunkt wie folgt:

ID  2008_A_TOTAL    2008_A_VOLUME   2008_B_TOTAL    2008_B_VOLUME   2008_C_TOTAL    2008_C_VOLUME   2009_A_TOTAL    2009_A_VOLUME   2009_B_TOTAL    2009_B_VOLUME   2009_C_TOTAL    2009_C_VOLUME
DD1 1000            10              2000            20              3000            30              4000            40              5000            50              6000            60
DD2 7000            70              8000            80              9000            90              10000           100             11000           110             12000           120

Mein SQL Server 2008 query wird wie folgt um die Tabelle zu erstellen:

CREATE TABLE ATM_TRANSACTIONS 
(
 ID varchar(5),
 T_YEAR varchar(4),
 T_TYPE varchar(3), 
 TOTAL int,
 VOLUME int
);

INSERT INTO ATM_TRANSACTIONS
(ID,T_YEAR,T_TYPE,TOTAL,VOLUME)

VALUES
('DD1','2008','A',1000,10),
('DD1','2008','B',2000,20),
('DD1','2008','C',3000,30),
('DD1','2009','A',4000,40),
('DD1','2009','B',5000,50),
('DD1','2009','C',6000,60),
('DD2','2008','A',7000,70),
('DD2','2008','B',8000,80),
('DD2','2008','C',9000,90),
('DD2','2009','A',10000,100),
('DD2','2009','B',11000,110),
('DD2','2009','C',1200,120);

Den T_Year Spalte kann sich ändern in der Zukunft, aber die T_TYPE Spalte ist in der Regel wissen,, so bin ich nicht sicher, ob ich kann mit einer Kombination der PIVOT-Funktion im SQL-Server mit dynamischen code?

Ich habe versucht, nach dem Beispiel hier:

http://social.technet.microsoft.com/wiki/contents/articles/17510.t-sql-dynamic-pivot-on-multiple-columns.aspx

aber ich landete mit mit seltsamen Ergebnissen.

InformationsquelleAutor Mkov | 2013-09-06

Um das Ergebnis zu erhalten, werden Sie benötigen, zu betrachten, unpivoting die Daten in der Total und Volume Spalten, die erste vor der Anwendung der PIVOT-Funktion, um das endgültige Ergebnis. Mein Vorschlag wäre, zuerst schreiben Sie eine hart codierte version der Abfrage dann konvertieren, um dynamische SQL.

Der UNPIVOT-Prozess wandelt diese mehrere Spalten in Zeilen. Es gibt ein paar Möglichkeiten, um UNPIVOT, können Sie mit der UNPIVOT-Funktion, oder Sie können die Verwendung von CROSS APPLY. Der code unpivot die Daten ähnlich:

select id, 
    col = cast(t_year as varchar(4))+'_'+t_type+'_'+col, 
    value
from ATM_TRANSACTIONS t
cross apply
(
    select 'total', total union all
    select 'volume', volume
) c (col, value);

Diese erhalten Sie Daten im format:

+-----+---------------+-------+
| id  |      col      | value |
+-----+---------------+-------+
| DD1 | 2008_A_total  |  1000 |
| DD1 | 2008_A_volume |    10 |
| DD1 | 2008_B_total  |  2000 |
| DD1 | 2008_B_volume |    20 |
| DD1 | 2008_C_total  |  3000 |
| DD1 | 2008_C_volume |    30 |
+-----+---------------+-------+

Dann können Sie die PIVOT-Funktion:

select ID, 
    [2008_A_total], [2008_A_volume], [2008_B_total], [2008_B_volume],
    [2008_C_total], [2008_C_volume], [2009_A_total], [2009_A_volume]
from
(
    select id, 
        col = cast(t_year as varchar(4))+'_'+t_type+'_'+col, 
        value
    from ATM_TRANSACTIONS t
    cross apply
    (
        select 'total', total union all
        select 'volume', volume
    ) c (col, value)
) d
pivot
(
    max(value)
    for col in ([2008_A_total], [2008_A_volume], [2008_B_total], [2008_B_volume],
                [2008_C_total], [2008_C_volume], [2009_A_total], [2009_A_volume])
) piv;

Nun, dass Sie die richtige Logik, können Sie konvertieren, um dynamische SQL:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(cast(t_year as varchar(4))+'_'+t_type+'_'+col) 
                    from ATM_TRANSACTIONS t
                    cross apply
                    (
                        select 'total', 1 union all
                        select 'volume', 2
                    ) c (col, so)
                    group by col, so, T_TYPE, T_YEAR
                    order by T_YEAR, T_TYPE, so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT id,' + @cols + ' 
            from 
            (
                select id, 
                    col = cast(t_year as varchar(4))+''_''+t_type+''_''+col, 
                    value
                from ATM_TRANSACTIONS t
                cross apply
                (
                    select ''total'', total union all
                    select ''volume'', volume
                ) c (col, value)
            ) x
            pivot 
            (
                max(value)
                for col in (' + @cols + ')
            ) p '

execute sp_executesql @query;

Dadurch erhalten Sie eine Ergebnis:

+-----+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+
| id  | 2008_A_total | 2008_A_volume | 2008_B_total | 2008_B_volume | 2008_C_total | 2008_C_volume | 2009_A_total | 2009_A_volume | 2009_B_total | 2009_B_volume | 2009_C_total | 2009_C_volume |
+-----+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+
| DD1 |         1000 |            10 |         2000 |            20 |         3000 |            30 |         4000 |            40 |         5000 |            50 |         6000 |            60 |
| DD2 |         7000 |            70 |         8000 |            80 |         9000 |            90 |        10000 |           100 |        11000 |           110 |         1200 |           120 |
+-----+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+--------------+---------------+

Fantastisch. Ich wusste nicht, dass ich brauchte, um unpivot/cross-gelten dann pivot. Dank so viel.
suppppppperrrrrrrrrrb
Das ist großartig. Es dauert ein wenig mehr Arbeit, um es mit dynamischen Spalten, aber die Allgemeine Idee hält immer noch.
Ich möchte zum erstellen von html-Tabellen, 1 Abfrage, füttern ein jquery-api eine dynamische Anzahl von Spalten zu verarbeiten und fand diese Abfrage, perfekt zu sein für meine Belastungen. +1

InformationsquelleAutor Taryn

declare @stmt nvarchar(max)

select @stmt = isnull(@stmt + ', ', '') + 
        'sum(case when T_YEAR = ''' + T.T_YEAR + ''' and T_TYPE = ''' + T.T_TYPE + ''' then TOTAL else 0 end) as ' + quotename(T.T_YEAR + '_' +  T.T_TYPE + '_TOTAL') + ',' +
        'sum(case when T_YEAR = ''' + T.T_YEAR + ''' and T_TYPE = ''' + T.T_TYPE + ''' then VOLUME else 0 end) as ' + quotename(T.T_YEAR + '_' +  T.T_TYPE + '_VOLUME')
from (select distinct T_YEAR, T_TYPE from ATM_TRANSACTIONS) as T
order by T_YEAR, T_TYPE

select @stmt = '
    select
        ID, ' + @stmt + ' from ATM_TRANSACTIONS group by ID'

exec sp_executesql
    @stmt = @stmt

leider sqlfiddle.com ist momentan nicht funktioniert, so kann ich nicht erstellen, ein Beispiel für Sie.

Die Abfrage erstellt durch dynamic SQL wäre:

select
    ID,
    sum(case when T_YEAR = '2008' and T_TYPE = 'A' then TOTAL else 0 end) as 2008_A_TOTAL,
    sum(case when T_YEAR = '2008' and T_TYPE = 'A' then VOLUME else 0 end) as 2008_A_VOLUME,
    ...
from ATM_TRANSACTIONS
group by ID

InformationsquelleAutor Roman Pekar

Bitte versuchen:

DECLARE @pivv NVARCHAR(MAX),@Query NVARCHAR(MAX)

SELECT @pivv=COALESCE(@pivv+',','')+ QUOTENAME(T_YEAR+'_'+T_TYPE+'_TOTAL')+','+QUOTENAME(T_YEAR+'_'+T_TYPE+'_VOLUME') from ATM_TRANSACTIONS GROUP BY T_YEAR, T_TYPE

IF ISNULL(@pivv, '')<>''
    SET @Query='SELECT * FROM(
        SELECT ID, T_YEAR+''_''+T_TYPE+''_TOTAL'' TYP, TOTAL VAL from ATM_TRANSACTIONS UNION
        SELECT ID, T_YEAR+''_''+T_TYPE+''_VOLUME'' TYP, VOLUME VAL from ATM_TRANSACTIONS 
        )x pivot (SUM(VAL) for TYP in ('+@pivv+')) as xx'

IF ISNULL(@Query, '')<>''
    EXEC (@Query)

InformationsquelleAutor TechDo

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.