SQL Pivot with dynamic generated columns and aggregate function

Question

I've got a Table

  CREATE TABLE [dbo].[newTable](
    [EBELN] [nvarchar](20) NOT NULL,
    [EBELP] [nvarchar](10) NOT NULL,
    [VGABE] [nvarchar](2) NOT NULL,
    [MENGE] [numeric](15, 3) NULL,
    [DMBTR] [numeric](15, 2) NULL

)

It has these records

insert into dbo.newTable(EBELN, EBELP, VGABE, MENGE , DMBTR) values('3000000004', '0001', '1', 1 , 27.95 )
Go
insert into dbo.newTable(EBELN, EBELP, VGABE, MENGE , DMBTR) values('3000000004', '0001', '2', 1 , 27.95 )
Go
insert into dbo.newTable(EBELN, EBELP, VGABE, MENGE , DMBTR) values('3000000004', '0002', '1', 1 , 10.95 )
Go
insert into dbo.newTable(EBELN, EBELP, VGABE, MENGE , DMBTR) values('3000000004', '0002', '2', 1 , 10.95 )
Go
insert into dbo.newTable(EBELN, EBELP, VGABE, MENGE , DMBTR) values('3000000010', '0001', '1', 1 , 22.95 )
Go
insert into dbo.newTable(EBELN, EBELP, VGABE, MENGE , DMBTR) values('3000000010', '0001', '2', 1 , 22.95 )
Go
insert into dbo.newTable(EBELN, EBELP, VGABE, MENGE , DMBTR) values('3000000010', '0002', '1', 1 , 32.95 )
Go
insert into dbo.newTable(EBELN, EBELP, VGABE, MENGE , DMBTR) values('3000000010', '0002', '2', 1 , 32.95 )
Go
insert into dbo.newTable(EBELN, EBELP, VGABE, MENGE , DMBTR) values('4151516119', '0001', '1', 1 , 400.00 )
Go
insert into dbo.newTable(EBELN, EBELP, VGABE, MENGE , DMBTR) values('4151516119', '0001', '1', 1 , 400.00 )
Go
insert into dbo.newTable(EBELN, EBELP, VGABE, MENGE , DMBTR) values('4151516119', '0001', '2', 1 , 400.00 )
Go
insert into dbo.newTable(EBELN, EBELP, VGABE, MENGE , DMBTR) values('4151516119', '0002', '1', 1 , 200.00 )
Go
insert into dbo.newTable(EBELN, EBELP, VGABE, MENGE , DMBTR) values('4151516119', '0002', '2', 1 , 200.00 )
Go

Thats the SELECT *

EBELN                EBELP      VGABE MENGE                                   DMBTR
-------------------- ---------- ----- -------------- ---------------------------------------
3000000004           0001       1     1.000                                   27.95
3000000004           0001       2     1.000                                   27.95
3000000004           0002       1     1.000                                   10.95
3000000004           0002       2     1.000                                   10.95
3000000010           0001       1     1.000                                   22.95
3000000010           0001       2     1.000                                   22.95
3000000010           0002       1     1.000                                   32.95
3000000010           0002       2     1.000                                   32.95
4151516119           0001       1     1.000                                   400.00
4151516119           0001       1     1.000                                   400.00
4151516119           0001       2     1.000                                   400.00
4151516119           0002       1     1.000                                   200.00
4151516119           0002       2     1.000                                   200.00
3000000004           0001       2     1.000                                   27.95
3000000004           0002       1     1.000                                   10.95
3000000004           0002       2     1.000                                   10.95
3000000010           0001       1     1.000                                   22.95
3000000010           0001       2     1.000                                   22.95
3000000010           0002       1     1.000                                   32.95
3000000010           0002       2     1.000                                   32.95
4151516119           0001       1     1.000                                   400.00
4151516119           0001       1     1.000                                   400.00
4151516119           0001       2     1.000                                   400.00
4151516119           0002       1     1.000                                   200.00
4151516119           0002       2     1.000                                   200.00
4151516177           0002       6     1.000                                   111.00
4151516177           0002       8     1.000                                   111.00

What I need and want is a dynamic pivot which generates this result

+------------+-------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
|   EBELN    | EBELP | c_DMBTR_1 | c_MENGE_1 | c_DMBTR_2 | c_MENGE_2 | c_DMBTR_6 | c_MENGE_6 | c_DMBTR_8 | c_MENGE_8 |
+------------+-------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+
| 3000000004 |  0001 | 27.95     | 1         | 27.95     | 1         | NULL      | NULL      | NULL      | NULL      |
| 3000000004 |  0002 | 10.95     | 1         | 10.95     | 1         | NULL      | NULL      | NULL      | NULL      |
| [...]      |       |           |           |           |           |           |           |           |           |
| 4151516119 |  0001 | 800.00    | 1         | 400.00    | 1         | NULL      | NULL      | NULL      | NULL      |
| 4151516177 |  0002 | NULL      | NULL      | NULL      | NULL      | 111.00    | 1         | 111.00    | 1         |
+------------+-------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+-----------+

I tried several solution but none is getting the result i want to get.

What I need is the values in VGABE which are (1,2,6,7,8,9,P,R) to be concatenated to the string names of c_DMBTR and c_MENGE. But it's possible that for example P or 7 is not used, so I will not want to have the Columns for that. I think making it dynamic is the only possible way to do so.

The Value from VGABE should be added to the Column name like 'c_MENGE_'+VGABE and it must be in order, which means it has to start with lowest value from VGABE with all columns and then the next value till every used value from vgabe.

If there is for one position (EBELP) more than one Value from VGABE like two times 1 for POS 0001 as you can see by EBELN 4151516119 I have to SUM(DMBTR) for this position and VGABE Value. The same I have to do with MENGE.

Is it possible to do in one query or in a stored procedure. I don't know how to get this result, I'm stuck right now. Or is there another way to do which I'm not aware of?

Im using SQL Server 2014 and the MS SQL Management Studio 2014 — JollyPopper
– JollyPopper, Commented Oct 1, 2014 at 12:09

Taryn · Accepted Answer · 2014-10-01 11:24:30Z

2

In order to get the final result that you want, you'll first have to unpivot the DMBTR and MENGE columns and then apply the PIVOT function to convert the rows into columns.

You didn't mention which version of SQL Server you are using go I'm going to guess SQL Server 2005+. Starting in SQL Server 2005, the PIVOT function was made available but for UNPIVOT you could also use CROSS APPLY - which I think would be easier here.

Before diving into a dynamic SQL version, I'd always look to write a static version to get the logic correct, then convert that to dynamic SQL. The first step would be to convert the DMBTR and MENGE columns into rows:

select 
  t.ebeln,
  t.ebelp,
  new_col = c.orig_col + '_' + vgabe,
  c.value
from dbo.newTable t
cross apply
(
  select 'c_MENGE', menge union all
  select 'c_DMBTR', dmbtr
) c (orig_col, value);

See SQL Fiddle with Demo. This converts your data into:

|      EBELN | EBELP |   NEW_COL | VALUE |
|------------|-------|-----------|-------|
| 3000000004 |  0001 | c_MENGE_1 |     1 |
| 3000000004 |  0001 | c_DMBTR_1 | 27.95 |
| 3000000004 |  0001 | c_MENGE_2 |     1 |
| 3000000004 |  0001 | c_DMBTR_2 | 27.95 |
| 3000000004 |  0002 | c_MENGE_1 |     1 |
| 3000000004 |  0002 | c_DMBTR_1 | 10.95 |

As you can see you now have multiple rows, along with a new_col with the concatenated values of c_MENGE_1, etc which will be your the final columns.

Once you have this result, you can apply the PIVOT function:

select ebeln,
  ebelp,
  c_DMBTR_1, c_MENGE_1, c_DMBTR_2, c_MENGE_2,
  c_DMBTR_6, c_MENGE_6, c_DMBTR_8, c_MENGE_8
from
(
  select 
    t.ebeln,
    t.ebelp,
    new_col = c.orig_col + '_' + vgabe,
    c.value
  from dbo.newTable t
  cross apply
  (
    select 'c_MENGE', menge union all
    select 'c_DMBTR', dmbtr
  ) c (orig_col, value)
) d
pivot
(
  sum(value)
  for new_col in (c_DMBTR_1, c_MENGE_1, c_DMBTR_2, c_MENGE_2,
                  c_DMBTR_6, c_MENGE_6, c_DMBTR_8, c_MENGE_8)
) piv
order by ebeln, ebelp;

See SQL Fiddle with Demo.

Now that you've got the logic correct, you'll want to convert this to dynamic SQL. This will start by creating a sql string of your new column names. For this, you 'll use FOR XML PATH:

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

This is similar to the code that I used in the static version. It gets the list of the vgabe values, and concatenates it to the name of the 2 columns you want to PIVOT (DMBTR, MENGE). I also provide a sort order for these columns, so you can order them how you need. The full dynamic SQL code will be:

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

select @cols = STUFF((SELECT ',' + QUOTENAME(col + '_' + vgabe) 
                    from dbo.NewTable t
                    cross apply
                    (
                      select 'c_DMBTR', 1 union all
                      select 'c_MENGE', 2
                    ) 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 dbo.newTable t
      cross apply
      (
        select ''c_MENGE'', menge union all
        select ''c_DMBTR'', dmbtr
      ) c (orig_col, value)
     ) x
     pivot 
     (
       sum(value)
       for new_col in (' + @cols + N')
     ) p 
     order by ebeln, ebelp'

exec sp_executesql @query;

See SQL Fiddle with Demo. This gives a result:

|      EBELN | EBELP | C_DMBTR_1 | C_MENGE_1 | C_DMBTR_2 | C_MENGE_2 | C_DMBTR_6 | C_MENGE_6 | C_DMBTR_8 | C_MENGE_8 |
|------------|-------|-----------|-----------|-----------|-----------|-----------|-----------|-----------|-----------|
| 3000000004 |  0001 |     27.95 |         1 |     27.95 |         1 |    (null) |    (null) |    (null) |    (null) |
| 3000000004 |  0002 |     10.95 |         1 |     10.95 |         1 |    (null) |    (null) |    (null) |    (null) |
| 3000000010 |  0001 |     22.95 |         1 |     22.95 |         1 |    (null) |    (null) |    (null) |    (null) |
| 3000000010 |  0002 |     32.95 |         1 |     32.95 |         1 |    (null) |    (null) |    (null) |    (null) |
| 4151516119 |  0001 |       800 |         2 |       400 |         1 |    (null) |    (null) |    (null) |    (null) |
| 4151516119 |  0002 |       200 |         1 |       200 |         1 |       111 |         1 |       111 |         1 |

answered Oct 1, 2014 at 11:24

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

JollyPopper Over a year ago

Hey there! Thank you very much for your fast and detailed response! Especially for showing the logic in static and in the dynamic way! Very amazing. Im using SQL Server 2014 and the Management Studio 2014. May i ask you another question?

Taryn Over a year ago

Sure, what's the question?

JollyPopper Over a year ago

1) Where do i have to insert a LEFT JOIN with dbo.newTableHeader as my main table with EBELN, EBELP both as PK on dbo.newTable. 2) Is it possible to expand the query. I want to add a column named c_COUNT_X where X stands for each VGABE Value. It counts VGABE for every EBELP. Maybe i could make a CTE to prepare the table for my needs and use it for the join

Taryn Over a year ago

@JollyPopper Hmm, I'm not exactly sure what you are asking. Maybe you should Ask a new question and include the new details.

JollyPopper Over a year ago

Hmm i will try something out. I just read that its not possible to have more than one AGGREGATE in TSQL. I will ask a new question but first i have to try something out! Thank you for your time!

|

Collectives™ on Stack Overflow

SQL Pivot with dynamic generated columns and aggregate function

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related