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