Click here to Skip to main content
15,891,567 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Current query:

SELECT
BTTLA,
JPNUM,
MAX(ACTFINISH) as PreLastDone
FROM MAXIMO."BTV_ZZZ"
WHERE
SITEID ='XXXX'
AND JPNUM in ( 'A’,B’)
AND WOCLASS = 'ZZZ'
AND ACTFINISH > SYSDATE-3
Group By BTTLA,JPNUM
ORDER By BTTLA ASC

Which shows this DATA:

BTTLA PRELASTDONE JPNUM
103 20/12/2019 01:15 A
103 19/12/2019 12:16 B
105 19/12/2019 02:00 A
105 20/12/2019 05:44 B
106 19/12/2019 04:58 A

How can I modify the query to show the DATA like this, please?:

TLA JP: B JP: A
103 19/12/2019 12:16 20/12/2019 01:15
105 20/12/2019 05:44 19/12/2019 02:00

What I have tried:

SELECT
BTTLA,
JPNUM,
MAX(ACTFINISH) as PreLastDone
FROM MAXIMO."BTV_ZZZ"
WHERE
SITEID ='XXXX'
AND JPNUM in ( 'A’,B’)
AND WOCLASS = 'ZZZ'
AND ACTFINISH > SYSDATE-3
Group By BTTLA,JPNUM
ORDER By BTTLA ASC
Posted
Updated 20-Dec-19 2:17am

Run the query twice, once to gather only the A elements, then add a second to gather only the B elements. Then use a JOIN on the BTTLA value to combine the two together and return them as a single row.
 
Share this answer
 
What I would do is to populate a temp table with the original query, and then create a second query with 2 instances of that temp table joined to itself.

(13:03 UCT)The query is relatively simple, but I need to get on the road now and don't have time to type it out... I will update this answer in about an hour or so

(15:44 UTC) Update with code sample for above
SQL
DECLARE @Temp TABLE (Bttla INT, PreLastDone DATETIME, JpNum CHAR(1))

INSERT @Temp  (Bttla, JpNum, PreLastDone)
SELECT BTTLA, JPNUM, MAX(ACTFINISH) as PreLastDone
FROM   MAXIMO."BTV_ZZZ"
WHERE  SITEID ='XXXX'
AND    JPNUM in ('A', 'B')
AND    WOCLASS = 'ZZZ'
AND    ACTFINISH > SYSDATE-3
GROUP BY BTTLA, JPNUM


SELECT	TLA	= a.BTTLA
,	[JP: B]	= b.PreLastDone
,	[JP: A]	= a.PreLastDone
FROM       @Temp  a
INNER JOIN @Temp  b ON a.BTTLA = b.BTTLA
ORDER BY TLA
 
Share this answer
 
v2
Something like this should work:
SQL
WITH cteSource As
(
    SELECT
        BTTLA,
        JPNUM,
        MAX(ACTFINISH) as PreLastDone
    FROM 
        MAXIMO."BTV_ZZZ"
    WHERE
        SITEID ='XXXX'
    AND 
        JPNUM in ('A', 'B')
    AND 
        WOCLASS = 'ZZZ'
    AND 
        ACTFINISH > SYSDATE-3
    GROUP BY
        BTTLA,
        JPNUM
)
SELECT
    BTTLA As TLA,
    Max(CASE JPNUM WHEN 'A' THEN PreLastDone END) As [JP: A],
    Max(CASE JPNUM WHEN 'B' THEN PreLastDone END) As [JP: B]
FROM
    cteSource
GROUP BY
    BTTLA
ORDER BY
    BTTLA
;
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900