Hello. I am trying to create a crosstab query in QMF/DB2. I know that there is no Transform function so can this be done in QMF/DB2?

The fields I'm working with are Store#, CSG, and Order#. I would like the row headings to be the Store#, the column headings to be the CSG,

and I want to calculate the count of Order#.

Thanks,

James

The fields I'm working with are Store#, CSG, and Order#. I would like the row headings to be the Store#, the column headings to be the CSG,

and I want to calculate the count of Order#.

Thanks,

James

SELECT StoreNumber, CSG, count(OrderNumber) Count FROM ...

And you'd get results like

StoreNumber CSG Count

AAAAAAA A01 1

AAAAAAA G12 4

BBBBBBB A04 2

To Pivot (Crosstab) this in DB2 is a bit tricky, especially if there are a lot of stores. Can you live with each row being a single string? If so, recursive SQL will do this quite nicely. Otherwise, we'll need to be creative and the query source will grow with the number of stores (columns).

A single string, delimited by tabs, commas, semi-colons, etc. is pretty easy.

If this is going to feed into a spreadsheet, I'll suggest the single string per line. If it will populate a grid in your database client we'll need to build out the full SQL, or build meta-SQL. That might actually be easier.

I've got a query that should do the job. It writes the selected data out in a csv (comma delimited) file that's suitable for importing into Excel. The result of the query with the data described below is:

Store#, C01, C02, C03, C04

AAA000001, 2, 0, 0, 0

BBB000001, 1, 2, 0, 0

CCC000001, 2, 0, 1, 1

DDD000001, 3, 1, 1, 0

Will this work for you?

```
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;
```

I tested it by creating the table and data:

```
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."?

That question essentially asks whether all vertical CSG columns will provide order counts for the same CSGs or if they're simple ordinal positions in each row.

Regardless, in the provided test script, try replacing all "varchar (100)" with "varchar (200)" or maybe "varchar (500)". Lengths simply seem a bit short, so make them longer.

One of the oddities of recursive SQL in DB2 is that string (varchar) lengths are determined by the first result. The item can grow during the recursive process, but only up to that limit. The good news is that it's easy to fix be recasting the varchar item in the upper sub-query. Here's the same query with a longer string length for the returned item. I realize that I also didn't include the totals by line column, so I'll work that up and post it shortly.

The only change between the two queries is that the two lines above the UNION ALL statements recast the string to varchar(1000) instead of 100.

```
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;
```

This should be the last significant change (unless the needs change or I've misunderstood them). I've changed the sub-query names to have some meaning and added the totals column in the final results.

Here's a rundown of each of the sub-queries:

- csg_list -- generate a list of all the CSG names that will appear in the result.

- Store_Summary -- generate a list of all stores, applicable CSGs, and the number of invoices for the store.

- Results - generates the output string for each store.

- Heading - Generates the heading row.

You can see the results of each of them, and the steps taken to build the result by executing portions of the CTE.

- replace the comma and following text after the csg_list definition with "select * from csg_list" to see all of the csg values.

- replace the comma and following text after the Store_Summary definition with "select * from Store_Summary"

- replace the comma and following text after the Results definition with "Select * from Results"

- replace the comma and following text after the Heading definition with "Select * from Heading"

DB2 is very different from SQL Server in handling recursive SQL and CTE structure.

- Some of the tests above will fail on SQL Server as it rejects a CTE where all sub-queries aren't used, making testing difficult.

- As mentioned earlier, DB2 doesn't automatically extend string definitions, SQL Server does.

- DB2 will continue the recursive process until an answer is found or the stack overflows, SQL Server defaults to a depth of 100.

- The two sub-queries that produce the final result will need to be in the other order on SQL Server.

You probably don't need to know that, but if you have a SQL Server person on staff he'll need to know that.

```
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;
```

Good Luck!

Kent

WITH csg_list (rn, csg) as

(

SELECT row_number () over (order by csg) rn, csg

FROM (SELECT distinct csg FROM a) t3

)

SELECT * FROM csg_list;

If you see a blank or NULL value, modify the csg_list sub-query to be:

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

),

Can you select all of your store numbers from the table, sorting by name? If binary data is in one of the names it should sort to the top and/or bottom of the list.

Also, it looks like we'll need to swap the two items in the final query. DB2 for that level on the AS400 seems to unwind the stack differently than does DB2 on my system. But that's a minor cleanup for the end.

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

)

SELECT * FROM Store_Summary;

and

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

)

SELECT distinct store FROM Store_Summary;

What do we get from this:

```
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;
```

The missing data probably comes from something in the Results sub-query though.

```
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);
```

The comma separated results are intended for opening/importing into Excel.

Kent

You can also copy and paste the contents into Excel, but they'll probably all wind up in column A. If so, click on the Data tab and then click on the "Text To Columns" icon.

Either should work just fine.

Kent

Let me check the results and make sure that they line up in the text we're producing.

Using my (admittedly limited) sample data everything lines up fine. And I see zeroes (not NULL or blank).

When you select "Text to Columns", make sure you also select "delimited" and that the comma is the delimiter.

Also, when I save the output to a file as "sample.csv" and open it with Excel it also formats correctly.

Kent

When I run that on my version of DB2, I get the expected results (below). Of course, I don't have QMF available to me. I suspect that QMF is compacting consecutive spaces, but I can't test or prove that.

Tom. Do you have an idea of what's happening or what our next step should be?

Thanks,

Kent

```
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
```

We need to see the exact query that gets the bad result. Please post it as code (or text) and not as an image because we'll need to edit it.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.