MySQL Forums
Forum List  »  Newbie

transpose SQL-output
Posted by: John Martins
Date: February 13, 2019 08:09AM

Hello together,

I want to transpose this output:

PRIO A B C D GSL BY_B LRK LD ND GESAMT SORTIEREN
1+ 0 0 0 0 0 0 0 0 0 0 1
1 0 1 0 1 0 0 0 0 0 2 2
2 0 0 0 0 0 0 0 0 0 0 3
4 0 0 0 0 0 0 0 0 0 0 4
AG 37 102 71 55 34 56 0 7 6 368 6


in to this output:

Bereich 1+ 1 2 4 AG
A 0 0 0 0 37
B 0 1 0 0 102
C 0 0 0 0 71
D 0 1 0 0 55
GSL 0 0 0 0 34
BY_B 0 0 0 0 56
LRK 0 0 0 0 0
LD 0 0 0 0 7
ND 0 0 0 0 6
GESAMT 0 2 0 0 368
SORTIEREN 1 2 3 4 6

Here ist the select query for the first output:

SELECT (CASE WHEN h.prio BETWEEN 0 AND 5 THEN '1+' WHEN h.prio BETWEEN 6 AND 13 THEN '1' WHEN h.prio BETWEEN 14 AND 39 THEN '2' WHEN h.prio BETWEEN 40 AND 99 THEN '4' ELSE '5' END) Prio,
COUNT(DISTINCT(CASE WHEN gl.geraeteprofil=1 and h.bereich<>892 THEN h.filenr ELSE NULL END)) A,
COUNT(DISTINCT(CASE WHEN gl.geraeteprofil=2 THEN h.filenr ELSE NULL END)) B,
COUNT(DISTINCT(CASE WHEN gl.geraeteprofil=3 THEN h.filenr ELSE NULL END)) C,
COUNT(DISTINCT(CASE WHEN gl.geraeteprofil=6 THEN h.filenr ELSE NULL END)) D,
COUNT(DISTINCT(CASE WHEN h.bereich in (600,601,604) THEN h.filenr ELSE NULL END)) GSL,
COUNT(DISTINCT(CASE WHEN h.bereich=892 and gl.geraeteprofil<>1 THEN h.filenr ELSE NULL END)) BY_B,
COUNT(DISTINCT(CASE WHEN h.bereich in (490,491) THEN h.filenr ELSE NULL END)) LRK,
COUNT(DISTINCT(CASE WHEN gl.geraeteprofil in (8,9) THEN h.filenr ELSE NULL END)) LD,
COUNT(DISTINCT(CASE WHEN gl.geraeteprofil in (10,11) THEN h.filenr ELSE NULL END)) ND,
COUNT(DISTINCT(CASE WHEN (gl.geraeteprofil in (1,2,3,6,8,9,10,11)) THEN h.filenr WHEN h.bereich in (490,491,892,600,601,604) THEN h.filenr ELSE NULL END)) GESAMT,
MAX((CASE WHEN h.prio BETWEEN 0 AND 5 THEN 1 WHEN h.prio BETWEEN 6 AND 13 THEN 2 WHEN h.prio BETWEEN 14 AND 39 THEN 3 WHEN h.prio BETWEEN 40 AND 99 THEN 4 ELSE 5 END)) Sortieren
FROM geraeteprofil gl, handy h, Auftrag a, gasse g
WHERE (h.typ = 'A'
OR h.typ = 'U' )
AND h.status IN (2, 6)
AND h.filenr=a.FILENR
AND DECODE(substr ( a.quelle , 0 , 2 ), '**', '00', substr ( a.quelle , 0 , 2 )) = g.segment
AND DECODE(substr ( a.quelle , 3 , 2 ), '**', '00', substr ( a.quelle , 3 , 2 )) = g.zeile
AND gl.gassen LIKE '%' || LTRIM ( TO_CHAR ( g.segment , '00' ) ) || LTRIM ( TO_CHAR ( g.gassenr , '00' ) ) || '%'
GROUP BY (CASE WHEN h.prio BETWEEN 0 AND 5 THEN '1+' WHEN h.prio BETWEEN 6 AND 13 THEN '1' WHEN h.prio BETWEEN 14 AND 39 THEN '2' WHEN h.prio BETWEEN 40 AND 99 THEN '4' ELSE '5' END)
UNION
SELECT 'AG' Prio,
SUM(CASE WHEN gt.geraeteprofil=1 and t.bereich<>892 THEN 1 ELSE 0 END) A,
SUM(CASE WHEN gt.geraeteprofil=2 THEN 1 ELSE 0 END) B,
SUM(CASE WHEN gt.geraeteprofil=3 THEN 1 ELSE 0 END) C,
SUM(CASE WHEN gt.geraeteprofil=6 THEN 1 ELSE 0 END) D,
SUM(CASE WHEN t.bereich in (600,601,604) THEN 1 ELSE NULL END) GSL,
SUM(CASE WHEN t.bereich=892 and gt.geraeteprofil<>1 THEN 1 ELSE 0 END) BY_B,
SUM(CASE WHEN t.bereich in (490,491) THEN 1 ELSE 0 END) LRK,
COUNT(DISTINCT(CASE WHEN gt.geraeteprofil in (8,9) THEN ziellhmnr ELSE NULL END)) LD,
COUNT(DISTINCT(CASE WHEN gt.geraeteprofil in (10,11) THEN ziellhmnr ELSE NULL END)) ND,
SUM(CASE WHEN gt.geraeteprofil in (2,3,6,8,9,10,11) THEN 1 WHEN t.bereich<>892 and gt.geraeteprofil=1 THEN 1 WHEN t.bereich=892 and gt.geraeteprofil<>1 THEN 1 WHEN t.bereich in (490,491,600,601,604) THEN 1 ELSE 0 END) GESAMT,
6 Sortieren
FROM tabakt t, geraeteprofil gt, gasse g where t.ziellhmnr<>'00000000000'
AND t.anlagezeit BETWEEN (TO_CHAR(CURRENT_DATE , 'YYYYMMDD') || '000000') AND (TO_CHAR(CURRENT_DATE , 'YYYYMMDD') ||
'240000')
AND g.SEGMENT=to_number(substr(t.ort,1,2)) AND g.ZEILE=to_number(substr(t.ort,3,2))
AND gt.GASSEN like '%' || LTRIM ( TO_CHAR ( g.segment , '00' ) ) || LTRIM ( TO_CHAR ( g.gassenr , '00' ) ) || '%'
ORDER BY Sortieren;

I know this is possible with the functions pivot and unpivot, but I don' t know how to get it done.
I need help.

John

Options: ReplyQuote


Subject
Written By
Posted
transpose SQL-output
February 13, 2019 08:09AM
February 13, 2019 08:46AM
February 14, 2019 02:03AM
February 14, 2019 06:23AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.