Make Table query

[ashwinghanta]

Hello Everyone,

I have the following table and I want to create a table out of this

SYSTEM	EREIGNIS	DATUM_ZEIT	ANTRAGSNUMMER
WL	AUS_ED	1.1.2016	123
WL	AUS_ES	1.2.2016	123
ZW	EIN_ED	1.3.2016	123
ZW	EIN_ES	1.4.2016	123
WL	AUS_ED	1.5.2016	222
WL	AUS_ES	1.6.2016	222

<tbody>
</tbody>

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

ANTRAGSNUMMER	AusgangDatenstromWebLife	AusgangesigniertWebLife	EingangeDatenstromzworkflow	Ausgangesigniertzworkflow
123	1.1.2016	1.2.2016	1.3.2016	1.4.2016
222	1.5.2016	1.6.2016

<tbody>
</tbody>

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

ANTRAGSNUMMER	AusgangDatenstromWebLife	AusgangesigniertWebLife	EingangeDatenstromzworkflow	Ausgangesigniertzworkflow
123	1.1.2016	1.1.2016	1.1.2016	1.1.2016
222	1.2.2016	1.2.2016	1.2.2016

<tbody>
</tbody>

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?