Make Table query

[ashwinghanta]

Hello Everyone,

I have the following table and I want to create a table out of this
[TABLE="width: 500"]
<tbody>[TR]
[TD]SYSTEM[/TD]
[TD]EREIGNIS[/TD]
[TD]DATUM_ZEIT[/TD]
[TD]ANTRAGSNUMMER[/TD]
[/TR]
[TR]
[TD]WL[/TD]
[TD]AUS_ED[/TD]
[TD]1.1.2016[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]WL[/TD]
[TD]AUS_ES[/TD]
[TD]1.2.2016[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]ZW[/TD]
[TD]EIN_ED[/TD]
[TD]1.3.2016[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]ZW[/TD]
[TD]EIN_ES[/TD]
[TD]1.4.2016[/TD]
[TD]123[/TD]
[/TR]
[TR]
[TD]WL[/TD]
[TD]AUS_ED[/TD]
[TD]1.5.2016[/TD]
[TD]222[/TD]
[/TR]
[TR]
[TD]WL[/TD]
[TD]AUS_ES[/TD]
[TD]1.6.2016[/TD]
[TD]222[/TD]
[/TR]
</tbody>[/TABLE]

I want to use the maketable query which gives me a table like this

[TABLE="width: 500"]
<tbody>[TR]
[TD]ANTRAGSNUMMER[/TD]
[TD]AusgangDatenstromWebLife[/TD]
[TD]AusgangesigniertWebLife[/TD]
[TD]EingangeDatenstromzworkflow[/TD]
[TD]Ausgangesigniertzworkflow[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]1.1.2016[/TD]
[TD]1.2.2016[/TD]
[TD]1.3.2016[/TD]
[TD]1.4.2016[/TD]
[/TR]
[TR]
[TD]222[/TD]
[TD]1.5.2016[/TD]
[TD]1.6.2016[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I have tried with the following make table SQL Query

SELECT  VWDRSSTA.ANTRAGSNUMMER AS Antragsnummer, VWDRSSTA.DATUM_ZEIT AS AusgangDatenstromWebLife, VWDRSSTA.DATUM_ZEIT AS AusgangesigniertWebLife, VWDRSSTA.DATUM_ZEIT AS EingangeDatenstromzworkflow, VWDRSSTA.DATUM_ZEIT AS Ausgangesigniertzworkflow FROM VWDRSSTA WHERE (VWDRSSTA.SYSTEM ='WL' AND VWDRSSTA.EREIGNIS='AUS_ED') OR (VWDRSSTA.SYSTEM ='WL' AND VWDRSSTA.EREIGNIS='AUS_ES')  OR (VWDRSSTA.SYSTEM ='ZW' AND VWDRSSTA.EREIGNIS='EIN_ED')  OR (VWDRSSTA.SYSTEM ='ZW' AND VWDRSSTA.EREIGNIS='EIN_ES')

which gives me something like this

[TABLE="width: 500"]
<tbody>[TR]
[/TR]
[TR]
[TD]ANTRAGSNUMMER[/TD]
[TD]AusgangDatenstromWebLife[/TD]
[TD]AusgangesigniertWebLife[/TD]
[TD]EingangeDatenstromzworkflow[/TD]
[TD]Ausgangesigniertzworkflow[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]1.1.2016[/TD]
[TD]1.1.2016[/TD]
[TD]1.1.2016[/TD]
[TD]1.1.2016[/TD]
[/TR]
[TR]
[TD]222[/TD]
[TD]1.2.2016[/TD]
[TD]1.2.2016[/TD]
[TD]1.2.2016[/TD]
[/TR]
</tbody>[/TABLE]

Can someone tell me where am I going wrong in the code? Looking forward to hear from you

 

[xenou]

Hi,
I would recommend you build your cross tab queries in the query builder to get the right syntax. Probably you need something else in the group by clause since you are adding a new field to the query.

 

[ashwinghanta]

Hi,

I tried doing that using Group by

[COLOR=#333333]TRANSFORM Max(VWDRSSTA.DATUM_ZEIT) AS MaxOfDATUM_ZEIT[/COLOR]
[COLOR=#333333]SELECT VWDRSSTA.ANTRAGSNUMMER, VWDRSSTA.DOK_ART AS DOK[/COLOR]
[COLOR=#333333]FROM VWDRSSTA INNER JOIN V_NAMES [/COLOR]
[COLOR=#333333]ON (VWDRSSTA.SYSTEM = V_NAMES.SYSTEM_CODE) [/COLOR]
[COLOR=#333333]AND (VWDRSSTA.EREIGNIS = V_NAMES.EREIGNIS)[/COLOR]
[COLOR=#333333]WHERE VWDRSSTA.EREIGNIS = "EIN-ES"[/COLOR]
[COLOR=#333333]GROUP BY VWDRSSTA.ANTRAGSNUMMER,VWDRSSTA.DOK_ART[/COLOR]
[COLOR=#333333]ORDER BY VWDRSSTA.ANTRAGSNUMMER[/COLOR]
[COLOR=#333333]PIVOT V_NAMES.MAPPED_NAME;[/COLOR]

This code runs but It gives me the output of only

[COLOR=#333333][FONT=&quot][I]ZW | EIN_ES | Ausgangesigniertzworkflow |[/I][/FONT][/COLOR]

It does not show in other fields for that particular Antragsnummer. How should I regroup it so that I can also have the other fields?

 

[xenou]

Did you build your query in the query builder or just edit this by hand?