好，这里有些混乱，因为您想要将具有多行的多列转换为更多列，并且最需要的是为每个数据库使用不同的聚合，因为其中一些需要SUM，而另一些则必须使用MAX或MIN(在字符串/日期上).

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

您应该始终而不是直接研究动态sql版本，而是尝试使用较小版本的静态查询来正确获取逻辑.我认为，这使动态SQL的使用变得更加容易，因为您不会猜测要做什么或什么是行不通的.

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

请参见带演示的SQL小提琴.这将获得最终结果所需的所有值，因为您正在获取ebeln，ebelp和vgabe组合的总和.您将看到，我还将所有值都转换为相同的数据类型-这对于下一步是必要的-不可透视.由于所有数据都将存储在同一列中，因此它们需要相同的数据类型.

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)

请参见带演示的SQL小提琴.现在您得到的数据如下所示:

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 |

最后，您将应用PIVOT功能:

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;

请参见带演示的SQL小提琴.

现在您有了工作逻辑，就可以将其转换为动态sql:

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;

请参见带有演示的SQL小提琴.得到的最终结果是:

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