6

I have got the following query:

WITH preEKBE AS(
SELECT 
        EKPO . MANDT,
        EKPO . EBELN,
        EKPO . EBELP,
        DD07T.DDTEXT AS c_Meaning,
        EKBE . VGABE,
        EKBE . DMBTR,
        EKBE . MENGE, 
        COUNT(VGABE) OVER(PARTITION BY EKBE . EBELN, EKBE . EBELP, ZEKKN) AS c_COUNT,
        CONVERT (varchar(10),MIN(EKBE . BLDAT) OVER ( PARTITION BY EKBE . EBELN, EKBE . EBELP, EKBE . VGABE),104) AS c_EBKE_BLDAT_First,
        CONVERT (varchar(10),MIN(EKBE . BUDAT) OVER ( PARTITION BY EKBE . EBELN, EKBE . EBELP, EKBE . VGABE),104) AS c_EKBE_BUDAT_First,
        CONVERT (varchar(10),MAX(EKBE . BLDAT) OVER ( PARTITION BY EKBE . EBELN, EKBE . EBELP, EKBE . VGABE),104) AS c_EBKE_BLDAT_Last,
        CONVERT (varchar(10),MAX(EKBE . BUDAT) OVER ( PARTITION BY EKBE . EBELN, EKBE . EBELP, EKBE . VGABE),104) AS c_EKBE_BUDAT_Last
        FROM EKPO

LEFT JOIN EKKO 
        ON EKPO . MANDT = EKKO . MANDT
        AND EKPO . EBELN = EKKO . EBELN

LEFT JOIN EKBE
        ON EKPO . MANDT = EKBE . MANDT
        AND EKPO . EBELN = EKBE . EBELN
        AND EKPO . EBELP = EKBE . EBELP

LEFT JOIN DD07T
        ON DD07T . DOMNAME = 'VGABE'
        AND DD07T . DOMVALUE_L = EKBE.VGABE
        AND DD07T . DDLANGUAGE = 'D'
)

SELECT * INTO #preEKBE FROM preEKBE
ORDER BY EBELN , EBELP

It generates me this table

+-------+------------+-------+-----------------------------+-------+---------+----------+---------+--------------------+--------------------+-------------------+-------------------+
| MANDT |   EBELN    | EBELP |          c_Meaning          | VGABE |  DMBTR  |  MENGE   | c_COUNT | c_EBKE_BLDAT_First | c_EKBE_BUDAT_First | c_EBKE_BLDAT_Last | c_EKBE_BUDAT_Last |
+-------+------------+-------+-----------------------------+-------+---------+----------+---------+--------------------+--------------------+-------------------+-------------------+
|   800 | 3000000004 | 00001 | Wareneingang                |     1 | 27.95   | 1.000    |       1 | 19.12.2000         | 19.12.2000         | 19.12.2000        | 19.12.2000        |
|   800 | 3000000004 | 00001 | Rechnungseingang            |     2 | 27.95   | 1.000    |       1 | 19.12.2000         | 21.12.2000         | 19.12.2000        | 21.12.2000        |
|   800 | 3000000004 | 00002 | Wareneingang                |     1 | 10.95   | 1.000    |       1 | 19.12.2000         | 19.12.2000         | 19.12.2000        | 19.12.2000        |
|   800 | 3000000004 | 00002 | Rechnungseingang            |     2 | 10.95   | 1.000    |       1 | 19.12.2000         | 21.12.2000         | 19.12.2000        | 21.12.2000        |
|   800 | 4500008499 | 00010 | Wareneingang                |     1 | 268.43  | 1.000    |       1 | 27.03.2000         | 27.03.2000         | 27.03.2000        | 27.03.2000        |
|   800 | 4500008499 | 00010 | Leistungserfassungsblatt    |     9 | 268.43  | 1.000    |       1 | 27.03.2000         | 27.03.2000         | 27.03.2000        | 27.03.2000        |
|   800 | 4500010470 | 00010 | Wareneingang                |     1 | 0.00    | 1092.000 |       6 | 07.02.2001         | 07.02.2001         | 07.02.2001        | 07.02.2001        |
|   800 | 4500010470 | 00010 | Wareneingang                |     1 | 0.00    | 3512.000 |       6 | 07.02.2001         | 07.02.2001         | 07.02.2001        | 07.02.2001        |
|   800 | 4500010470 | 00010 | Warenausgabe für Umlagerung |     6 | 1615.52 | 3512.000 |       6 | 07.02.2001         | 07.02.2001         | 07.02.2001        | 07.02.2001        |
|   800 | 4500010470 | 00010 | Warenausgabe für Umlagerung |     6 | 502.32  | 1092.000 |       6 | 07.02.2001         | 07.02.2001         | 07.02.2001        | 07.02.2001        |
|   800 | 4500010470 | 00010 | Lieferung zu Umlagerung     |     8 | 0.00    | 1092.000 |       6 | 01.01.1900         | 07.02.2001         | 01.01.1900        | 07.02.2001        |
|   800 | 4500010470 | 00010 | Lieferung zu Umlagerung     |     8 | 0.00    | 3512.000 |       6 | 01.01.1900         | 07.02.2001         | 01.01.1900        | 07.02.2001        |
+-------+------------+-------+-----------------------------+-------+---------+----------+---------+--------------------+--------------------+-------------------+-------------------+

Now I've got a dynamic Pivot that partially works.

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

select @cols = STUFF((SELECT ',' + QUOTENAME(col + '_' + VGABE) 
                    from #preEKBE t
                    cross apply
                    (                    
                      select 'c_DMBTR', 1 union all
                      select 'c_MENGE', 2 union all
                      select 'c_COUNT', 3     
                    ) c (col, so)
                    group by col, so, VGABE
                    order by VGABE, so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query 
  = 'SELECT EBELN, EBELP,' + @cols + N' 
     from
     (
      select
        t.EBELN,
        t.EBELP,
        new_col = c.orig_col + ''_'' + VGABE,
        c.value
      from #preEKBE t

      cross apply
      (
        select ''c_MENGE'', t.MENGE union all
        select ''c_DMBTR'', t.DMBTR union all
        select ''c_COUNT'', t.c_COUNT 
      ) c (orig_col, value)
     ) x
     pivot 
     (
       sum(value)
       for new_col in (' + @cols + N')
     ) p 
     order by EBELN , EBELP' 
exec sp_executesql @query;

Giving me a result:

+------------+-------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
|   EBELN    | EBELP | c_DMBTR_1 | c_MENGE_1 | c_COUNT_1 | c_DMBTR_2 | c_MENGE_2 | c_COUNT_2 | c_DMBTR_6 | c_MENGE_6 | c_COUNT_6 | c_DMBTR_7 | c_MENGE_7 | c_COUNT_7 | c_DMBTR_8 | c_MENGE_8 | c_COUNT_8 | c_DMBTR_9 | c_MENGE_9 | c_COUNT_9 | c_DMBTR_P | c_MENGE_P | c_COUNT_P | c_DMBTR_R | c_MENGE_R | c_COUNT_R |
+------------+-------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 3000000004 | 00001 | 27.950    | 1.000     | 1.000     | 27.950    | 1.000     | 1.000     | NULL      | NULL      | NULL      | NULL      | NULL      | NULL      | NULL      | NULL      | NULL      | NULL      | NULL      | NULL      | NULL      | NULL      | NULL      | NULL      | NULL      | NULL      |
| 3000000004 | 00002 | 10.950    | 1.000     | 1.000     | 10.950    | 1.000     | 1.000     | NULL      | NULL      | NULL      | NULL      | NULL      | NULL      | NULL      | NULL      | NULL      | NULL      | NULL      | NULL      | NULL      | NULL      | NULL      | NULL      | NULL      | NULL      |
+------------+-------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+

I need the query to be dynamic because of the Column VGABE to create the new column names and there can be values that are not used, and I only want the VGABE values being used.

Now the problem is that I want to add more columns which also should be generated dynamically. When there is a VGABE with 1 than I need a column called c_Meaning_1 (nvarchar) and it will have the value from the join with DD07T.

The c_COUNT_ stands for the Count of each VGABE Value per record. This column works perfectly.

After those columns I also need to add the columns c_BLDAT_First_, c_BUDAT_First_, c_BLDAT_Last_, and c_BUDAT_Last_ with the value of VGABE concatenated on the end of the new column name. This value is calculated in the CTE.

Is there a way to use the CTE directly without a temporary table?

I'm not sure how to solve this since I'm dealing with multiple datatypes and they each would be aggregated differently. When there are the values 1 and 9 for VGABE then it should look like this:

+---------------+-----------+-----------+-----------+----------------------+----------------------+---------------------+---------------------+---------------------------+------------+-----------+-----------+----------------------+----------------------+---------------------+---------------------+
|  C_MEANING_1  | C_DMBTR_1 | C_MENGE_1 | C_COUNT_1 | C_EBKE_BLDAT_FIRST_1 | C_EKBE_BUDAT_FIRST_1 | C_EBKE_BLDAT_LAST_1 | C_EKBE_BUDAT_LAST_1 |        C_MEANING_9        | C_DMBTR_9  | C_MENGE_9 | C_COUNT_9 | C_EBKE_BLDAT_FIRST_9 | C_EKBE_BUDAT_FIRST_9 | C_EBKE_BLDAT_LAST_9 | C_EKBE_BUDAT_LAST_9 |
+---------------+-----------+-----------+-----------+----------------------+----------------------+---------------------+---------------------+---------------------------+------------+-----------+-----------+----------------------+----------------------+---------------------+---------------------+
| Wareneingang: | 10,00     |         1 |         1 | 19.12.2000           | 19.12.2000           | 19.12.2000          | 19.12.2000          | Leistungserfassungsblatt: |          0 |         0 |         0 | NULL                 | NULL                 | NULL                | NULL                |
| Wareneingang: | 0         |         0 |         0 | NULL                 | NULL                 | NULL                | NULL                | Leistungserfassungsblatt: |         20 |         2 |         1 | 19.12.2000           | 19.12.2000           | 19.12.2000          | 19.12.2000          |
+---------------+-----------+-----------+-----------+----------------------+----------------------+---------------------+---------------------+---------------------------+------------+-----------+-----------+----------------------+----------------------+---------------------+---------------------+

For each VGABE value there should be a own column in the order given above. If you need further information just ask me please. I'm using SQL Server 2014 with SQL Management Studio 2014 and TSQL.

Taryn
  • 242,637
  • 56
  • 362
  • 405
JollyPopper
  • 85
  • 1
  • 8
  • Maybe I'm not fully understanding the question, but if you want to only return rows associated with `VGABE = 1`, then you should be able to add a `WHERE` filter to the dynamic column generation. Then you'd also include the other columns that you want (`c_EBKE_BLDAT_First`, `c_EKBE_BUDAT_First`, etc) in your subquery of the pivot and the final select list similar to this [demo](http://sqlfiddle.com/#!3/58e57/7). If that isn't what you want, then you might have to update the question to make it clearer. – Taryn Oct 02 '14 at 11:13
  • The query should make the columns for each value in VGABE. So when there are the values (1,3,7,R) in VGABE then it should create the colums beginning with the lowest value_-> c_Meaning_X c_DMBTR_X c_MENGE_X c_COUNT_X c c_BLDAT_First_X c_BUDAT_First_X c_BLDAT_Last_X c_BUDAT_Last_X (where X stands for the value in VGABE). The colums must be in this order. – JollyPopper Oct 02 '14 at 11:32
  • The tricky thing here to do is that the content in c_Meaning_X depends on the value in VGABE and is in the table DD07T (join). If there is for example VGABE 9 then it have to create the column c_Meaning_9 with content "Leistungserfassungsblatt" from DD07T.DDTEXT for every record whether there is only one record with vgabe 9 and in the others not. – JollyPopper Oct 02 '14 at 11:32
  • When you delete the `WHERE` filter in your demo then you get the colums in the right order. The last task would be to add the `c_Meaning_X` in front of the columns with the same `VGABE` value and after `c_COUNT_X` the colums with the `DATE` `(c_EBKE_BLDAT_First_X, c_EKBE_BUDAT_First_X)` – JollyPopper Oct 02 '14 at 12:04

1 Answers1

8

Well you've got a bit of a mess here because you want to convert multiple columns with multiple rows onto more columns and on top of that you'll need to use a different aggregate for each because some of them you need to SUM and others you'll have to use MAX or MIN (on the string/dates).

Instead of diving right into a dynamic sql version you should always try to get the logic correct using a smaller version of a static query. This makes working with dynamic SQL much easier, in my opinion, because you aren't guessing at what to do or what isn't working right.

I'd first start with a query that gets the total of each item you need to SUM.

select ebeln, ebelp, 
  c_Meaning, 
  vgabe, 
  dmbtr = cast(sum(dmbtr) over(partition by ebeln, ebelp, vgabe) as varchar(50)),
  menge = cast(sum(menge) over(partition by ebeln, ebelp, vgabe) as varchar(50)),
  c_count = cast(sum(c_count) over(partition by ebeln, ebelp, vgabe) as varchar(50)),
  c_EBKE_BLDAT_First = cast(c_EBKE_BLDAT_First as varchar(50)), 
  c_EKBE_BUDAT_First = cast(c_EKBE_BUDAT_First as varchar(50)),
  c_EBKE_BLDAT_Last = cast(c_EBKE_BLDAT_Last as varchar(50)),
  c_EKBE_BUDAT_Last = cast(c_EKBE_BUDAT_Last as varchar(50))
from preEKBE

See SQL Fiddle with Demo. This gets the values of everything you'll need in the final result because you are getting the sum over the combination of ebeln, ebelp, and vgabe. You'll see that I also cast all of the values into the same datatype - this is necessary for the next step - the unpivot. Since all of the data will be stored in the same column, they need the same datatypes.

select d.ebeln, d.ebelp,
  new_col = c.orig_col + '_' + cast(d.vgabe as varchar(2)),
  c.value
from
(
  select ebeln, ebelp, 
    c_Meaning, 
    vgabe, 
    dmbtr = cast(sum(dmbtr) over(partition by ebeln, ebelp, vgabe) as varchar(50)),
    menge = cast(sum(menge) over(partition by ebeln, ebelp, vgabe) as varchar(50)),
    c_count = cast(sum(c_count) over(partition by ebeln, ebelp, vgabe) as varchar(50)),
    c_EBKE_BLDAT_First = cast(c_EBKE_BLDAT_First as varchar(50)), 
    c_EKBE_BUDAT_First = cast(c_EKBE_BUDAT_First as varchar(50)),
    c_EBKE_BLDAT_Last = cast(c_EBKE_BLDAT_Last as varchar(50)),
    c_EKBE_BUDAT_Last = cast(c_EKBE_BUDAT_Last as varchar(50))
  from preEKBE
) d
cross apply 
(
  select 'c_Meaning', c_Meaning union all
  select 'c_MENGE', menge union all
  select 'c_DMBTR', dmbtr union all
  select 'c_count', c_count union all
  select 'c_EBKE_BLDAT_First', c_EBKE_BLDAT_First union all
  select 'c_EKBE_BUDAT_First', c_EKBE_BUDAT_First union all
  select 'c_EBKE_BLDAT_Last', c_EBKE_BLDAT_Last union all
  select 'c_EKBE_BUDAT_Last', c_EKBE_BUDAT_Last
) c (orig_col, value)

See SQL Fiddle with Demo. Now you've got data that looks like this:

|      EBELN | EBELP |              NEW_COL |                       VALUE |
|------------|-------|----------------------|-----------------------------|
| 3000000004 |     1 |          c_Meaning_1 |                Wareneingang |
| 3000000004 |     1 |            c_MENGE_1 |                           1 |
| 3000000004 |     1 |            c_DMBTR_1 |                       27.95 |
| 3000000004 |     1 |            c_count_1 |                           1 |
| 3000000004 |     1 | c_EBKE_BLDAT_First_1 |                  19.12.2000 |
| 3000000004 |     1 | c_EKBE_BUDAT_First_1 |                  19.12.2000 |

Finally, you'd apply the PIVOT function:

select ebeln, 
  ebelp,
  c_Meaning_1, c_MENGE_1, c_DMBTR_1, c_count_1,
  c_EBKE_BLDAT_First_1, c_EKBE_BUDAT_First_1,
  c_EBKE_BLDAT_Last_1, c_EKBE_BUDAT_Last_1
from
(
  select d.ebeln, d.ebelp,
    new_col = c.orig_col + '_' + cast(d.vgabe as varchar(2)),
    c.value
  from
  (
    select ebeln, ebelp, 
      c_Meaning, 
      vgabe, 
      dmbtr = cast(sum(dmbtr) over(partition by ebeln, ebelp, vgabe) as varchar(50)),
      menge = cast(sum(menge) over(partition by ebeln, ebelp, vgabe) as varchar(50)),
      c_count = cast(sum(c_count) over(partition by ebeln, ebelp, vgabe) as varchar(50)),
      c_EBKE_BLDAT_First = cast(c_EBKE_BLDAT_First as varchar(50)), 
      c_EKBE_BUDAT_First = cast(c_EKBE_BUDAT_First as varchar(50)),
      c_EBKE_BLDAT_Last = cast(c_EBKE_BLDAT_Last as varchar(50)),
      c_EKBE_BUDAT_Last = cast(c_EKBE_BUDAT_Last as varchar(50))
    from preEKBE
  ) d
  cross apply 
  (
    select 'c_Meaning', c_Meaning union all
    select 'c_MENGE', menge union all
    select 'c_DMBTR', dmbtr union all
    select 'c_count', c_count union all
    select 'c_EBKE_BLDAT_First', c_EBKE_BLDAT_First union all
    select 'c_EKBE_BUDAT_First', c_EKBE_BUDAT_First union all
    select 'c_EBKE_BLDAT_Last', c_EBKE_BLDAT_Last union all
    select 'c_EKBE_BUDAT_Last', c_EKBE_BUDAT_Last
  ) c (orig_col, value)
) src
pivot
(
  max(value)
  for new_col in (c_Meaning_1, c_MENGE_1, c_DMBTR_1, c_count_1,
                  c_EBKE_BLDAT_First_1, c_EKBE_BUDAT_First_1,
                  c_EBKE_BLDAT_Last_1, c_EKBE_BUDAT_Last_1)
) piv;

See SQL Fiddle with Demo.

Now that you have working logic, you can convert this to dynamic sql:

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

select @cols = STUFF((SELECT ',' + QUOTENAME(col + '_' + cast(VGABE as varchar(2))) 
                    from preEKBE t
                    cross apply
                    (   
                      select 'c_meaning', 0 union all
                      select 'c_DMBTR', 1 union all
                      select 'c_MENGE', 2 union all
                      select 'c_COUNT', 3 union all
                      select 'c_EBKE_BLDAT_FIRST', 4 union all
                      select 'c_EKBE_BUDAT_FIRST', 5 union all
                      select 'c_EBKE_BLDAT_LAST', 6 union all
                      select 'c_EKBE_BUDAT_LAST', 7
                    ) c (col, so)
                    group by col, so, VGABE
                    order by VGABE, so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query 
  = 'SELECT EBELN, EBELP, ' + @cols + N' 
     from
     (
      select d.ebeln, 
        d.ebelp,
        new_col = c.orig_col + ''_'' + cast(d.vgabe as varchar(2)),
        c.value
      from
      (
        select ebeln, ebelp, 
          c_Meaning, 
          vgabe, 
          dmbtr = cast(sum(dmbtr) over(partition by ebeln, ebelp, vgabe) as varchar(50)),
          menge = cast(sum(menge) over(partition by ebeln, ebelp, vgabe) as varchar(50)),
          c_count = cast(sum(c_count) over(partition by ebeln, ebelp, vgabe) as varchar(50)),
          c_EBKE_BLDAT_First = cast(c_EBKE_BLDAT_First as varchar(50)), 
          c_EKBE_BUDAT_First = cast(c_EKBE_BUDAT_First as varchar(50)),
          c_EBKE_BLDAT_Last = cast(c_EBKE_BLDAT_Last as varchar(50)),
          c_EKBE_BUDAT_Last = cast(c_EKBE_BUDAT_Last as varchar(50))
        from preEKBE
      ) d
      cross apply
      (
        select ''c_meaning'', d.c_meaning union all
        select ''c_MENGE'', d.MENGE union all
        select ''c_DMBTR'', d.DMBTR union all
        select ''c_COUNT'', d.c_COUNT union all
        select ''c_EBKE_BLDAT_First'', d.c_EBKE_BLDAT_First union all
        select ''c_EKBE_BUDAT_First'', d.c_EKBE_BUDAT_First union all
        select ''c_EBKE_BLDAT_Last'', d.c_EBKE_BLDAT_Last union all
        select ''c_EKBE_BUDAT_Last'', d.c_EKBE_BUDAT_Last
      ) c (orig_col, value)
     ) x
     pivot 
     (
       max(value)
       for new_col in (' + @cols + N')
     ) p 
     order by EBELN , EBELP' 

exec sp_executesql @query;

See SQL Fiddle with Demo. This gives a final result of:

|      EBELN | EBELP |  C_MEANING_1 | C_DMBTR_1 | C_MENGE_1 | C_COUNT_1 | C_EBKE_BLDAT_FIRST_1 | C_EKBE_BUDAT_FIRST_1 | C_EBKE_BLDAT_LAST_1 | C_EKBE_BUDAT_LAST_1 |      C_MEANING_2 | C_DMBTR_2 | C_MENGE_2 | C_COUNT_2 | C_EBKE_BLDAT_FIRST_2 | C_EKBE_BUDAT_FIRST_2 | C_EBKE_BLDAT_LAST_2 | C_EKBE_BUDAT_LAST_2 |                 C_MEANING_6 | C_DMBTR_6 | C_MENGE_6 | C_COUNT_6 | C_EBKE_BLDAT_FIRST_6 | C_EKBE_BUDAT_FIRST_6 | C_EBKE_BLDAT_LAST_6 | C_EKBE_BUDAT_LAST_6 |             C_MEANING_8 | C_DMBTR_8 | C_MENGE_8 | C_COUNT_8 | C_EBKE_BLDAT_FIRST_8 | C_EKBE_BUDAT_FIRST_8 | C_EBKE_BLDAT_LAST_8 | C_EKBE_BUDAT_LAST_8 |              C_MEANING_9 | C_DMBTR_9 | C_MENGE_9 | C_COUNT_9 | C_EBKE_BLDAT_FIRST_9 | C_EKBE_BUDAT_FIRST_9 | C_EBKE_BLDAT_LAST_9 | C_EKBE_BUDAT_LAST_9 |
|------------|-------|--------------|-----------|-----------|-----------|----------------------|----------------------|---------------------|---------------------|------------------|-----------|-----------|-----------|----------------------|----------------------|---------------------|---------------------|-----------------------------|-----------|-----------|-----------|----------------------|----------------------|---------------------|---------------------|-------------------------|-----------|-----------|-----------|----------------------|----------------------|---------------------|---------------------|--------------------------|-----------|-----------|-----------|----------------------|----------------------|---------------------|---------------------|
| 3000000004 |     1 | Wareneingang |     27.95 |         1 |         1 |           19.12.2000 |           19.12.2000 |          19.12.2000 |          19.12.2000 | Rechnungseingang |     27.95 |         1 |         1 |           19.12.2000 |           21.12.2000 |          19.12.2000 |          21.12.2000 |                      (null) |    (null) |    (null) |    (null) |               (null) |               (null) |              (null) |              (null) |                  (null) |    (null) |    (null) |    (null) |               (null) |               (null) |              (null) |              (null) |                   (null) |    (null) |    (null) |    (null) |               (null) |               (null) |              (null) |              (null) |
| 3000000004 |     2 | Wareneingang |     10.95 |         1 |         1 |           19.12.2000 |           19.12.2000 |          19.12.2000 |          19.12.2000 | Rechnungseingang |     10.95 |         1 |         1 |           19.12.2000 |           21.12.2000 |          19.12.2000 |          21.12.2000 |                      (null) |    (null) |    (null) |    (null) |               (null) |               (null) |              (null) |              (null) |                  (null) |    (null) |    (null) |    (null) |               (null) |               (null) |              (null) |              (null) |                   (null) |    (null) |    (null) |    (null) |               (null) |               (null) |              (null) |              (null) |
| 4500008499 |    10 | Wareneingang |    268.43 |         1 |         1 |           27.03.2000 |           27.03.2000 |          27.03.2000 |          27.03.2000 |           (null) |    (null) |    (null) |    (null) |               (null) |               (null) |              (null) |              (null) |                      (null) |    (null) |    (null) |    (null) |               (null) |               (null) |              (null) |              (null) |                  (null) |    (null) |    (null) |    (null) |               (null) |               (null) |              (null) |              (null) | Leistungserfassungsblatt |    268.43 |         1 |         1 |           27.03.2000 |           27.03.2000 |          27.03.2000 |          27.03.2000 |
| 4500010470 |    10 | Wareneingang |      0.00 |      4604 |        12 |           07.02.2001 |           07.02.2001 |          07.02.2001 |          07.02.2001 |           (null) |    (null) |    (null) |    (null) |               (null) |               (null) |              (null) |              (null) | Warenausgabe für Umlagerung |   2117.84 |      4604 |        12 |           07.02.2001 |           07.02.2001 |          07.02.2001 |          07.02.2001 | Lieferung zu Umlagerung |      0.00 |      4604 |        12 |           01.01.1900 |           07.02.2001 |          01.01.1900 |          07.02.2001 |                   (null) |    (null) |    (null) |    (null) |               (null) |               (null) |              (null) |              (null) |
Taryn
  • 242,637
  • 56
  • 362
  • 405
  • 4
    Holy sh** dude! You are soo amazing. Thats it what i was talking about. I tried the think with the MAX() but didnt SUM() every record before the MAX(). Excellent, thank you very much for your patience and time reading my cryptic post. Im wondering how can i solve the issue with the NULL values in c_Meaning. Once there is a column with c_Meaning_1 i want that for each record there is the value "Wareneingang:" and so on.. – JollyPopper Oct 02 '14 at 13:29
  • 2
    There's no Markdown to emphasise the word `always` enough. Some people simply don't seem to get it. – Andriy M Oct 03 '14 at 05:46