SQL Pivot with dynamically created columns, aggregate function, and non-aggregate columns

Question

I have 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 this table for me.

 +-------+------------+-------+-----------------------------+-------+---------+----------+---------+--------------------+--------------------+-------------------+-------------------+ | 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 have 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; 

I give me the 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 due to the VGABE column in order to create new column names, and there may be values ​​that are not used, and I only need VGABE values.

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

c_COUNT_ means the amount of each VGABE value for each record. This column works great.

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

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

I am not sure how to solve this, since I am dealing with several types of data, and each of them will be aggregated in different ways. If VGABE has values ​​1 and 9, 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 | +---------------+-----------+-----------+-----------+----------------------+----------------------+---------------------+---------------------+---------------------------+------------+-----------+-----------+----------------------+----------------------+---------------------+---------------------+ 

Each VGABE value must have its own column in the order above. If you need more information, just ask me, please. I am using SQL Server 2014 with SQL Management Studio 2014 and TSQL.

Answer 1

You have a bit of a mess here because you want to convert multiple columns with multiple rows to more columns, and in addition, you will need to use different aggregates for each, because some of them you need SUM and others, you will have to use MAX or MIN (in string / dates).

Instead of diving directly into the dynamic version of sql, you should always try to get the correct logic using a smaller version of the static query. This makes working with dynamic SQL a lot easier, in my opinion, because you have no idea what to do or what does not work correctly.

First, I start with a query that gets the total amount of each item needed for 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 need in the end result, because you get the amount for a combination of ebeln , ebelp and vgabe . You will see that I also included all the values ​​in the same data type - this is necessary for the next step - univot. Since all data will be stored in one column, they need the same data types.

 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 . You now have 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 would 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 it 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 the final result:

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