Advertisement
Guest User

Untitled

a guest
Apr 8th, 2020
168
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 0.74 KB | None | 0 0
  1. WITH t1 AS
  2. (
  3.     SELECT
  4.         rownum AS rownumm,
  5.         ROW_NUMBER() OVER (partition BY c.maker ORDER BY c.TYPE) rang,
  6.         maker,
  7.         TYPE,
  8.         CASE
  9.             WHEN TYPE LIKE 'PC' THEN 1
  10.             WHEN TYPE LIKE 'Laptop' THEN 2
  11.             WHEN TYPE LIKE 'Printer' THEN 3 ELSE 0
  12.         END AS type_n
  13.     FROM
  14.     (
  15.         SELECT DISTINCT
  16.             maker, TYPE
  17.         FROM Product
  18.         ORDER BY maker
  19.     ) c
  20. )
  21.  
  22. SELECT
  23.     f.rownumm AS num,
  24.     CASE
  25.         WHEN f.rang = 1 THEN f.maker ELSE ''
  26.     END AS maker,
  27.     s.TYPE
  28. FROM
  29.     t1 f
  30.     LEFT JOIN
  31.     (
  32.         SELECT
  33.             rang,
  34.             maker,
  35.             TYPE,
  36.             type_n,
  37.             CASE WHEN type_n IN (SELECT rang FROM t1 aa WHERE aa.maker = bb.maker AND aa.TYPE_N = bb.RANG) THEN type_n ELSE rang END AS rr
  38.         FROM t1 bb
  39.     ) s ON f.maker = s.maker AND f.rang = s.rr
  40.     ORDER BY rownumm
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement