Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
WITH g (rn, csg) as
(
SELECT row_number () over (order by csg) rn, csg
FROM (SELECT distinct csg FROM a) t3
),
b (rn, store, csg, count) as
(
SELECT row_number () over (partition by t0.store order by t0.store, t1.csg) RN, t0.store, t1.csg, t2.count
FROM (select distinct store from a) t0 cross join g t1
LEFT JOIN (SELECT store, csg, count(ordernumber) count FROM a group by store, csg) t2
ON t0.store = t2.store
AND t1.csg = t2.csg
),
c (ln, store, text) as
(
SELECT 1, b.store, cast (b.store as varchar (100)) FROM b WHERE rn = 1
UNION ALL
SELECT ln+1, c.store, text || ', ' || cast (coalesce (count,0) as varchar(5))
FROM c, b
WHERE c.store = b.store
AND c.ln = b.rn
),
H (tn, text) as
(
SELECT 1, cast('Store#' as varchar (100)) text from sysibm.sysdummy1
UNION ALL
SELECT tn+1, text || ', ' || csg
FROM g, h
WHERE tn = rn
)
SELECT Text
FROM c
WHERE c.ln = (SELECT count(*) from g) + 1
UNION ALL
SELECT text FROM H
WHERE h.tn = (SELECT count(*) from g) + 1;
CREATE TABLE a (store varchar(20), csg varchar(10), ordernumber integer)
INSERT INTO a values ('AAA000001', 'C01', 1)
INSERT INTO a values ('AAA000001', 'C01', 2)
INSERT INTO a values ('BBB000001', 'C01', 3)
INSERT INTO a values ('BBB000001', 'C02', 4)
INSERT INTO a values ('BBB000001', 'C02', 5)
INSERT INTO a values ('CCC000001', 'C01', 6)
INSERT INTO a values ('CCC000001', 'C03', 7)
INSERT INTO a values ('CCC000001', 'C01', 8)
INSERT INTO a values ('CCC000001', 'C04', 9)
INSERT INTO a values ('DDD000001', 'C01', 10)
INSERT INTO a values ('DDD000001', 'C01', 11)
INSERT INTO a values ('DDD000001', 'C02', 12)
INSERT INTO a values ('DDD000001', 'C03', 13)
INSERT INTO a values ('DDD000001', 'C01', 14)
COMMIT
For example, for SZ3279, CSG B15 had 5 orders, CSG B16 had 1779 orders, CSG B17 had 465 orders, etc.So would the column headings then be "CSG(B15) CSG(B16) CSG(B17) ... etc."? Or would they remain as "CSG(1) CSG(2) CSG(3) CSG(4) etc."?
WITH g (rn, csg) as
(
SELECT row_number () over (order by csg) rn, csg
FROM (SELECT distinct csg FROM a) t3
),
b (rn, store, csg, count) as
(
SELECT row_number () over (partition by t0.store order by t0.store, t1.csg) RN, t0.store, t1.csg, t2.count
FROM (select distinct store from a) t0 cross join g t1
LEFT JOIN (SELECT store, csg, count(ordernumber) count FROM a group by store, csg) t2
ON t0.store = t2.store
AND t1.csg = t2.csg
),
c (ln, store, text) as
(
SELECT 1, b.store, cast (b.store as varchar (1000)) FROM b WHERE rn = 1
UNION ALL
SELECT ln+1, c.store, text || ', ' || cast (coalesce (count,0) as varchar(5))
FROM c, b
WHERE c.store = b.store
AND c.ln = b.rn
),
H (tn, text) as
(
SELECT 1, cast('Store#' as varchar (1000)) text from sysibm.sysdummy1
UNION ALL
SELECT tn+1, text || ', ' || csg
FROM g, h
WHERE tn = rn
)
SELECT Text
FROM c
WHERE c.ln = (SELECT count(*) from g) + 1
UNION ALL
SELECT text FROM H
WHERE h.tn = (SELECT count(*) from g) + 1;
WITH csg_list (rn, csg) as
(
SELECT row_number () over (order by csg) rn, csg
FROM (SELECT distinct csg FROM a) t3
),
Store_Summary (rn, store, csg, count) as
(
SELECT row_number () over (partition by t0.store order by t0.store, t1.csg) RN, t0.store, t1.csg, t2.count
FROM (select distinct store from a) t0 cross join csg_list t1
LEFT JOIN (SELECT store, csg, count(ordernumber) count FROM a group by store, csg) t2
ON t0.store = t2.store
AND t1.csg = t2.csg
),
Results (ln, store, total, text) as
(
SELECT 1 ln, Store_Summary.store, 0 Total, cast (Store_Summary.store as varchar (400)) FROM Store_Summary WHERE rn = 1
UNION ALL
SELECT ln+1, c.store, Total+coalesce(count,0), text || ', ' || cast (coalesce (count,0) as varchar(5))
FROM Results c, Store_Summary b
WHERE c.store = b.store
AND c.ln = b.rn
),
Heading (tn, text) as
(
SELECT 1, cast('Store#' as varchar (400)) text from sysibm.sysdummy1
UNION ALL
SELECT tn+1, text || ', ' || csg
FROM csg_list, Heading
WHERE tn = rn
)
SELECT Text || ', ' || cast (total as varchar(10))
FROM Results c
WHERE c.ln = (SELECT count(*) from csg_list) + 1
UNION ALL
SELECT text || ', Total'
FROM Heading
WHERE Heading.tn = (SELECT count(*) from csg_list) + 1;
WITH csg_list (rn, csg) as
(
SELECT row_number () over (order by csg) rn, csg
FROM (SELECT distinct csg FROM a) t3
),
Store_Summary (rn, store, csg, count) as
(
SELECT row_number () over (partition by t0.store order by t0.store, t1.csg) RN, t0.store, t1.csg, t2.count
FROM (select distinct store from a) t0 cross join csg_list t1
LEFT JOIN (SELECT store, csg, count(ordernumber) count FROM a group by store, csg) t2
ON t0.store = t2.store
AND t1.csg = t2.csg
),
Results (ln, store, total, text) as
(
SELECT 1 ln, Store_Summary.store, 0 Total, cast (Store_Summary.store as varchar (400)) FROM Store_Summary WHERE rn = 1
UNION ALL
SELECT ln+1, c.store, Total+coalesce(count,0), text || ', ' || cast (coalesce (count,0) as varchar(5))
FROM Results c, Store_Summary b
WHERE c.store = b.store
AND c.ln = b.rn
)
SELECT * FROM Results;
WITH csg_list (rn, csg) as
(
SELECT row_number () over (order by csg) rn, csg
FROM (SELECT distinct csg FROM a WHERE csg <> ' ' AND csg is not NULL) t3
),
Store_Summary (rn, store, csg, count) as
(
SELECT row_number () over (partition by t0.store order by t0.store, t1.csg) RN, t0.store, t1.csg, t2.count
FROM (select distinct store from a) t0 cross join csg_list t1
LEFT JOIN (SELECT store, csg, count(ordernumber) count FROM a group by store, csg) t2
ON t0.store = t2.store
AND t1.csg = t2.csg
),
Results (ln, store, total, text) as
(
SELECT 1 ln, Store_Summary.store, 0 Total, cast (Store_Summary.store as varchar (400)) FROM Store_Summary WHERE rn = 1
UNION ALL
SELECT ln+1, c.store, Total+coalesce(count,0), text || ', ' || cast (coalesce (count,0) as varchar(5))
FROM Results c, Store_Summary b
WHERE c.store = b.store
AND c.ln = b.rn
),
Heading (tn, text) as
(
SELECT 1, cast('Store#' as varchar (400)) text from sysibm.sysdummy1
UNION ALL
SELECT tn+1, text || ', ' || csg
FROM csg_list, Heading
WHERE tn = rn
)
SELECT text || ', Total'
FROM Heading
WHERE Heading.tn = (SELECT count(*) from csg_list) + 1
UNION ALL
SELECT Text || ', ' || cast (total as varchar(10))
FROM Results c
WHERE c.ln = (SELECT count(*) from csg_list) + 1;
WITH csg_list (rn, csg) as
(
SELECT row_number () over (order by csg) rn, csg
FROM (SELECT distinct csg FROM a WHERE csg <> ' ' AND csg is not NULL) t3
),
Store_Summary (rn, store, csg, count) as
(
SELECT row_number () over (partition by t0.store order by t0.store, t1.csg) RN, t0.store, t1.csg, t2.count
FROM (select distinct store from a) t0 cross join csg_list t1
LEFT JOIN (SELECT store, csg, count(ordernumber) count FROM a group by store, csg) t2
ON t0.store = t2.store
AND t1.csg = t2.csg
),
Results (ln, store, total, text) as
(
SELECT 1 ln, Store_Summary.store, 0 Total, cast (Store_Summary.store as varchar (400)) FROM Store_Summary WHERE rn = 1
UNION ALL
SELECT ln+1, c.store, Total+coalesce(count,0), text || ', ' || cast (coalesce (count,0) as varchar(5))
FROM Results c, Store_Summary b
WHERE c.store = b.store
AND c.ln = b.rn
),
Heading (tn, text) as
(
SELECT 1, cast('Store#' as varchar (400)) text from sysibm.sysdummy1
UNION ALL
SELECT tn+1, text || ', ' || csg
FROM csg_list, Heading
WHERE tn = rn
)
SELECT text || ', Total'
FROM Heading
WHERE Heading.tn = (SELECT count(*) from csg_list) + 1
UNION ALL
SELECT Text || ', ' || cast (total as varchar(10))
FROM Results r
WHERE r.ln = (SELECT max(ln) from Result r2 WHERE r.store = r2.store);
WITH csg_list (rn, csg) as
(
SELECT row_number () over (order by csg) rn, csg
FROM (SELECT distinct csg FROM a WHERE csg <> ' ' AND csg is not NULL) t3
),
Store_Summary (rn, store, csg, count) as
(
SELECT row_number () over (partition by t0.store order by t0.store, t1.csg) RN, t0.store, t1.csg, t2.count
FROM (select distinct store from a) t0 cross join csg_list t1
LEFT JOIN (SELECT store, csg, count(ordernumber) count FROM a group by store, csg) t2
ON t0.store = t2.store
AND t1.csg = t2.csg
),
Results (ln, store, total, text) as
(
SELECT 1 ln, Store_Summary.store, 0 Total, cast (Store_Summary.store as varchar (400)) FROM Store_Summary WHERE rn = 1
UNION ALL
SELECT ln+1, c.store, Total+coalesce(count,0), text || ', ' || cast (coalesce (count,0) as varchar(5))
FROM Results c, Store_Summary b
WHERE c.store = b.store
AND c.ln = b.rn
),
Footing (ln, text) as
(
SELECT 0 ln, cast ('Totals' as varchar (400)) FROM sysibm.sysdummy1
UNION ALL
SELECT ln+1, Text || ', ' || cast ((SELECT coalesce (SUM (count), 0) FROM Store_Summary SS WHERE SS.csg = csg_list.csg) as varchar (10))
FROM csg_list, Footing
WHERE rn = ln+1
),
Heading (tn, text) as
(
SELECT 1, cast('Store#' as varchar (400)) text from sysibm.sysdummy1
UNION ALL
SELECT tn+1, text || ', ' || csg
FROM csg_list, Heading
WHERE tn = rn
)
SELECT text || ', Total'
FROM Heading
WHERE Heading.tn = (SELECT count(*) from csg_list) + 1
UNION ALL
SELECT Text || ', ' || cast (total as varchar(10))
FROM Results r
WHERE r.ln = (SELECT max(ln) from Results r2 WHERE r.store = r2.store)
UNION ALL
SELECT Text || ', ' || cast ((SELECT sum(count) FROM Store_Summary) as varchar(10))
FROM Footing F
WHERE f.ln = (SELECT max(ln) FROM footing);
WITH csg_list (rn, csg) as
(
SELECT row_number () over (order by csg) rn, csg
FROM (SELECT distinct csg FROM a WHERE csg <> ' ' AND csg is not NULL) t3
),
Store_Summary (rn, store, csg, count) as
(
SELECT row_number () over (partition by t0.store order by t0.store, t1.csg) RN, t0.store, t1.csg, t2.count
FROM (select distinct store from a) t0 cross join csg_list t1
LEFT JOIN (SELECT store, csg, count(ordernumber) count FROM a group by store, csg) t2
ON t0.store = t2.store
AND t1.csg = t2.csg
),
Results (ln, store, total, text) as
(
SELECT 1 ln, Store_Summary.store, 0 Total, cast (cast (Store_Summary.store as char(10)) as varchar (400)) FROM Store_Summary WHERE rn = 1
UNION ALL
SELECT ln+1, c.store, Total+coalesce(count,0), text || ' | ' || right (' ' || cast (coalesce (count,0) as varchar(8)), 8)
FROM Results c, Store_Summary b
WHERE c.store = b.store
AND c.ln = b.rn
),
Footing (ln, text) as
(
SELECT 0 ln, cast (cast ('Totals' as char(10)) as varchar (400)) FROM sysibm.sysdummy1
UNION ALL
SELECT ln+1, Text || ' | ' || right (' ' || cast ((SELECT coalesce (SUM (count), 0) FROM Store_Summary SS WHERE SS.csg = csg_list.csg) as varchar (8)), 8)
FROM csg_list, Footing
WHERE rn = ln+1
),
Heading (tn, text) as
(
SELECT 1, cast(cast ('Store#' as char(10)) as varchar (400)) text from sysibm.sysdummy1
UNION ALL
SELECT tn+1, text || ' | ' || cast (csg as char(8))
FROM csg_list, Heading
WHERE tn = rn
)
SELECT text || ' | Total'
FROM Heading
WHERE Heading.tn = (SELECT count(*) from csg_list) + 1
UNION ALL
SELECT Text || ' | ' || right (' ' || cast (total as varchar(8)), 8)
FROM Results r
WHERE r.ln = (SELECT max(ln) from Results r2 WHERE r.store = r2.store)
UNION ALL
SELECT Text || ' | ' || right (' ' || cast ((SELECT sum(count) FROM Store_Summary) as varchar(10)), 8)
FROM Footing F
WHERE f.ln = (SELECT max(ln) FROM footing);
Store# | C01 | C02 | C03 | C04 | Total
AAA000001 | 2 | 0 | 0 | 0 | 2
BBB000001 | 1 | 2 | 0 | 0 | 3
CCC000001 | 2 | 0 | 1 | 1 | 4
DDD000001 | 3 | 1 | 1 | 0 | 5
Totals | 8 | 3 | 2 | 1 | 14
db2 => select * from a;
STORE CSG ORDERNUMBER
-------------------- ---------- -----------
AAA000001 C01 1
AAA000001 C01 2
BBB000001 C01 3
BBB000001 C02 4
BBB000001 C02 5
CCC000001 C01 6
CCC000001 C03 7
CCC000001 C01 8
CCC000001 C04 9
DDD000001 C01 10
DDD000001 C01 11
DDD000001 C02 12
DDD000001 C03 13
DDD000001 C01 14
Join the community of 500,000 technology professionals and ask your questions.