Focal Point
Converting rows to columns
April 25, 2007, 02:25 PM
getitConverting rows to columns
HI ,
I have searched thru the columns and found columns to rows conversion , but not the other way.
I have a table that has following value
id rank number
1 A 1001
1 B 1002
1 C 1003
2 A 1004
2 B 1005
I need to convert it to
id rank 'A' rank 'B'
1 1001 1004
2 1002 1005
Does anyone have any suggestions. I tried using define but could not eliminate extra rows that come with for example rank C.
App Studio Version 8202
windows Platform
SQL Server 2008/2012
April 25, 2007, 02:28 PM
Francis MarianiWouldn't
TABLE FILE ...
SUM NUMBER
BY ID
ACROSS RANK
END
do the job?
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
April 25, 2007, 02:39 PM
getitNO,
I need to be able to identify the across column.
its part of a bigger problem.
App Studio Version 8202
windows Platform
SQL Server 2008/2012
April 25, 2007, 02:49 PM
getitHere is my code
JOIN LIKP_VBELN IN LIKP TO MULTIPLE LIPS_VBELN IN LIPS AS J0
JOIN LIPS_VBELN IN LIKP TO VBUK_VBELN IN VBUK AS J1
JOIN LIPS_VGBEL IN LIKP TO VBAK_VBELN IN VBAK AS J2
JOIN VBAK_VBELN IN LIKP TO MULTIPLE VBPA_VBELN IN VBPA AS J3
DEFINE FILE LIKP
SHIPTO/A100 = IF VBPA_PARVW EQ 'WE' THEN VBPA_KUNNR ELSE '';
CARRIER/A100 = IF VBPA_PARVW EQ 'TU' THEN VBPA_LIFNR ELSE '';
END
TABLE FILE LIKP
PRINT
SHIPTO
CARRIER
BY VBPA_VBELN
WHERE VBUK_WBSTK = 'C'
AND VBPA_VBELN = '0000000051'
AND LIKP_WADAT FROM '20050101' TO '20070501'
END
and my output looks like
SD Doc. SHIPTO CARRIER
51......................
..........................
..........................
..........................700001
..........1000007
but I need
SD Doc. SHIPTO CARRIER
51 1000007 700001
The blank spaces occur because VBPA_PARVW has other values. If I do a sum instead of print, I am getting blanks.
SD Doc. SHIPTO CARRIER
51
App Studio Version 8202
windows Platform
SQL Server 2008/2012
April 25, 2007, 03:02 PM
KevinGWould
SUM
MAX.SHIPTO
MAX.CARRIER
BY VBPA_VBELN
work to remove your blank spaces?
Kevin
WF 7.6.10 / WIN-AIX
April 25, 2007, 03:05 PM
Francis MarianiHow do you know that SHIPTO 1000007 and CARRIER 700001 belong to SD Doc. 51?
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
April 25, 2007, 03:10 PM
getitOk, Max. did it.,that was easy. thanks Kevin.
Thanks Francis too for enquiring into this.
App Studio Version 8202
windows Platform
SQL Server 2008/2012