<div>
Hi James, 
 
Can you provide a brief description of what the table(s) look like? 
 
Thanks...
</div>

<div>
Hi Kent, 
 
It is just one table named MRR. The Store# is a 7 digit alpha numeric field, the CSG is a 3 digit alpha numeric field, and the Order# is a 14 digit alpha numeric field. Is this what you needed? 
Thanks, 
James
</div>

<div>
The traditional query would look something like this: 
 
&nbsp; SELECT StoreNumber, CSG, count(OrderNumber) Count FROM ... 
 
And you'd get results like 
 
&nbsp; StoreNumber &nbsp; CSG &nbsp; Count 
&nbsp; &nbsp;AAAAAAA &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A01 &nbsp; &nbsp; 1 
&nbsp; &nbsp;AAAAAAA &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;G12 &nbsp; &nbsp; 4 
&nbsp; &nbsp;BBBBBBB &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;A04 &nbsp; &nbsp; 2 
 
To Pivot (Crosstab) this in DB2 is a bit tricky, especially if there are a lot of stores. &nbsp;Can you live with each row being a single string? &nbsp;If so, recursive SQL will do this quite nicely. &nbsp;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.
</div>

<div>
Kent, 
 
I would like the end result to look like this: 
 
Store# &nbsp; &nbsp; &nbsp;CSG(1) &nbsp;CSG(2) &nbsp; CSG(3) &nbsp;CSG(4) &nbsp;etc... 
szd3279 &nbsp; &nbsp; 1000 &nbsp; &nbsp; &nbsp; &nbsp;550 &nbsp; &nbsp; &nbsp; &nbsp; 772 &nbsp; &nbsp; &nbsp; &nbsp;124 &nbsp; &nbsp; etc... 
uyt0019 &nbsp; &nbsp; &nbsp; &nbsp;623 &nbsp; &nbsp; &nbsp; &nbsp;429 &nbsp; &nbsp; &nbsp; &nbsp; 333 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;44 &nbsp; &nbsp; etc... 
 
There are only up to 6 Store#'s and up to about 30 CSG's.
</div>

<div>
Kent, 
 
&nbsp;I forgot to add a totals column at the end: 
 
&nbsp;Store# &nbsp; &nbsp; &nbsp;CSG(1) &nbsp;CSG(2) &nbsp; CSG(3) &nbsp;CSG(4) &nbsp;etc... &nbsp;Total 
&nbsp;szd3279 &nbsp; &nbsp; 1000 &nbsp; &nbsp; &nbsp; &nbsp;550 &nbsp; &nbsp; &nbsp; &nbsp; 772 &nbsp; &nbsp; &nbsp; &nbsp;124 &nbsp; &nbsp; etc... &nbsp;2446 
&nbsp;uyt0019 &nbsp; &nbsp; &nbsp; &nbsp;623 &nbsp; &nbsp; &nbsp; &nbsp;429 &nbsp; &nbsp; &nbsp; &nbsp; 333 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;44 &nbsp; &nbsp; etc... &nbsp;1429 
 
&nbsp;There are only up to 6 Store#'s and up to about 30 CSG's.
</div>

<div>
Is that up to 30 CSGs per store (implying up to 180 columns) or 30 combinations? 
 
If this is going to feed into a spreadsheet, I'll suggest the single string per line. &nbsp;If it will populate a grid in your database client we'll need to build out the full SQL, or build meta-SQL. &nbsp;That might actually be easier.
</div>

<div>
Kent, 
 
It would be 30 possible combinations, only 30 columns max. I am working in QMF and would be exporting it to a word or excel document. Hoping that the end result would look like the attached file. 
<a href="https://filedb.experts-exchange.com/incoming/2017/03_w12/1152370/QMF-Qry-Ex1.xlsx" target="_blank" class="file-inline" title="QMF-Qry-Ex1.xlsx (9 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>QMF-Qry-Ex1.xlsx</a>
</div>

QMF-Qry-Ex1.xlsx

<div>
Hi James, 
 
I've got a query that should do the job. &nbsp;It writes the selected data out in a csv (comma delimited) file that's suitable for importing into Excel. &nbsp;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? 
 

<pre><code class="code-10 nolinks" id="code-20-42060399-1">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;
</code></pre>
 
I tested it by creating the table and data: 
 

<pre><code class="code-10 nolinks" id="code-20-42060399-2">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
</code></pre>
</div>

<div>
Kent, 
 
I am getting the following error: 
 
BIC0004. DAL01008. An error occurred while accessing the database. VALUE SZ3279, 5, 1779, 465, 1614, 2777, 160, 110, 3, 338, 2173, 5, 8, 36, 3 IS TOO LONG. SQL CODE=-433, SQLSTATE=2201,DRIVER=4.17.29
</div>

<div>
You said &quot;CSG is a 3 digit alpha numeric field&quot;, but you're showing us multiple CSG values that apparently have 4 digits. Please provide actual database column definitions. Thirty 3-digit values account for 90 positions, but thirsty 4-digit values need a larger space, 120 positions. Declaring lengths requires knowing some details.
</div>

<div>
The CSG is a 3 digit alpha numeric such as B15, B16, B17, etc. The values that the error message shows are the total number (count) of the orders for the store# (SZ3279) by each CSG. For example, for SZ3279, CSG B15 had 5 orders, CSG B16 had 1779 orders, CSG B17 had 465 orders, etc. There are no thirsty 4 digit CSG values.
</div>

<div>
Ah. Those are order counts... so &quot;CSG(1)&quot; doesn't mean &quot;the 1st CSG&quot;, and below that the order count is 1000 for that CSG. 
 

<blockquote>
For example, for SZ3279, CSG B15 had 5 orders, CSG B16 had 1779 orders, CSG B17 had 465 orders, etc.
</blockquote>
So would the column headings then be &quot;CSG(B15) &nbsp;CSG(B16) &nbsp;CSG(B17) ... etc.&quot;? Or would they remain as &quot;CSG(1) &nbsp;CSG(2) &nbsp; CSG(3) &nbsp;CSG(4) &nbsp;etc.&quot;? 
 
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 &quot;varchar (100)&quot; with &quot;varchar (200)&quot; or maybe &quot;varchar (500)&quot;. Lengths simply seem a bit short, so make them longer.
</div>

<div>
Hi James, 
 
One of the oddities of recursive SQL in DB2 is that string (varchar) lengths are determined by the first result. &nbsp;The item can grow during the recursive process, but only up to that limit. &nbsp;The good news is that it's easy to fix be recasting the varchar item in the upper sub-query. &nbsp;Here's the same query with a longer string length for the returned item. &nbsp;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. 
 

<pre><code class="code-10 nolinks" id="code-20-42061294-1">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;
</code></pre>
</div>

<div>
Hi James, 
 
This should be the last significant change (unless the needs change or I've misunderstood them). &nbsp;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 &quot;select * from csg_list&quot; to see all of the csg values. 
- replace the comma and following text after the Store_Summary definition with &quot;select * from Store_Summary&quot; 
- replace the comma and following text after the Results definition with &quot;Select * from Results&quot; 
- replace the comma and following text after the Heading definition with &quot;Select * from Heading&quot; 
 
DB2 is very different from SQL Server in handling recursive SQL and CTE structure. &nbsp; 
- 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. 
 

<pre><code class="code-10 nolinks" id="code-20-42061480-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;
</code></pre>
 
 
Good Luck! 
Kent
</div>

<div>
Kent, 
 
When I run this query, only the header line (I think) is returned (see attachment). What could I be doing wrong? 
 
Thanks everyone for your help. 
 
James 
<a href="https://filedb.experts-exchange.com/incoming/2017/03_w12/1152532/2017-03-23_7-44-18.png" target="_blank" class="file-inline" title="Results (6 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>2017-03-23_7-44-18.png</a>
</div>

2017-03-23_7-44-18.png

<div>
It looks like a data issue. &nbsp;Note that the second item in the header is blank, suggesting one or more store has a blank or NULL for a csg value (I suspect NULL). &nbsp;Let's check it with the first sub-query in the CTE. 
 
WITH csg_list (rn, csg) as 
( 
&nbsp; SELECT row_number () over (order by csg) rn, csg 
&nbsp; 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 
( 
&nbsp; SELECT row_number () over (order by csg) rn, csg 
&nbsp; FROM (SELECT distinct csg FROM a WHERE csg &lt;&gt;&nbsp;' ' AND csg is not NULL) t3 
),
</div>

<div>
There was a blank and I modified the query as you instructed. I now get 2 lines of data, the header line and data for the first store. I've attached the results. 
 
Thank you, 
 
James 
<a href="https://filedb.experts-exchange.com/incoming/2017/03_w12/1152541/2017-03-23_8-19-19.png" target="_blank" class="file-inline" title="Results (9 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>2017-03-23_8-19-19.png</a>
</div>

2017-03-23_8-19-19.png

<div>
When I run just the csg_list and Store_summary subqueries together, I get 932 lines of data, but at line 79 the store# becomes 'Ÿ&amp;#0;&amp;#0;&amp;#0;&amp;#0;&amp;#0;'. I've attached the results. 
 
Thanks, 
 
James 
<a href="https://filedb.experts-exchange.com/incoming/2017/03_w12/1152555/2017-03-23_8-34-40.png" target="_blank" class="file-inline" title="2017-03-23_8-34-40.png (52 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>2017-03-23_8-34-40.png</a> 
<a href="https://filedb.experts-exchange.com/incoming/2017/03_w12/1152556/2017-03-23_8-35-19.png" target="_blank" class="file-inline" title="2017-03-23_8-35-19.png (56 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>2017-03-23_8-35-19.png</a> 
<a href="https://filedb.experts-exchange.com/incoming/2017/03_w12/1152557/2017-03-23_8-35-45.png" target="_blank" class="file-inline" title="2017-03-23_8-35-45.png (51 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>2017-03-23_8-35-45.png</a>
</div>

2017-03-23_8-34-40.png

2017-03-23_8-35-19.png

2017-03-23_8-35-45.png

<div>
That's odd. &nbsp;It looks like there may be binary data in one of the store numbers. 
 
Can you select all of your store numbers from the table, sorting by name? &nbsp;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. &nbsp;DB2 for that level on the AS400 seems to unwind the stack differently than does DB2 on my system. &nbsp;But that's a minor cleanup for the end.
</div>

<div>
Ok, I figured out my mistake and the 'Ÿ&amp;#0;&amp;#0;&amp;#0;&amp;#0;&amp;#0;' is gone. Now I am getting the 2 lines again when running the query. See the attached results. 
 
Thanks, 
 
James 
<a href="https://filedb.experts-exchange.com/incoming/2017/03_w12/1152566/2017-03-23_9-21-47.png" target="_blank" class="file-inline" title="2017-03-23_9-21-47.png (10 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>2017-03-23_9-21-47.png</a>
</div>

2017-03-23_9-21-47.png

<div>
There are only 4 stores currently in my table: SZ3279, SZD429, UYP003, &amp;&nbsp;UYT001.
</div>

<div>
There are only 4 stores currently in my table: SZ3279, SZD429, UYP003, &amp;&nbsp;UYT001. 
There are currently 27 CSGs in my table (BK1 through ZZZ). 
There are a total of 15,121 orders in my table. 
 
Thanks, 
 
James
</div>

<div>
I'm not sure why we're not seeing all 4 stores. &nbsp;What do you get when you run just the first two sub-queries? 
 
WITH csg_list (rn, csg) as 
( 
&nbsp; SELECT row_number () over (order by csg) rn, csg 
&nbsp; FROM (SELECT distinct csg FROM a) t3 
), 
Store_Summary (rn, store, csg, count) as 
( 
&nbsp; SELECT row_number () over (partition by t0.store order by t0.store, t1.csg) RN, t0.store, t1.csg, t2.count 
&nbsp; FROM (select distinct store from a) t0 cross join csg_list t1 
&nbsp; LEFT JOIN (SELECT store, csg, count(ordernumber) count FROM a group by store, csg) t2 
&nbsp; &nbsp; ON t0.store = t2.store 
&nbsp; &nbsp;AND t1.csg = t2.csg 
) 
SELECT * FROM Store_Summary; 
 
and 
 
WITH csg_list (rn, csg) as 
( 
&nbsp; SELECT row_number () over (order by csg) rn, csg 
&nbsp; FROM (SELECT distinct csg FROM a) t3 
), 
Store_Summary (rn, store, csg, count) as 
( 
&nbsp; SELECT row_number () over (partition by t0.store order by t0.store, t1.csg) RN, t0.store, t1.csg, t2.count 
&nbsp; FROM (select distinct store from a) t0 cross join csg_list t1 
&nbsp; LEFT JOIN (SELECT store, csg, count(ordernumber) count FROM a group by store, csg) t2 
&nbsp; &nbsp; ON t0.store = t2.store 
&nbsp; &nbsp;AND t1.csg = t2.csg 
) 
SELECT distinct store FROM Store_Summary;
</div>

<div>
When running the first query, I get 78 lines. 
 
When running the second query, I get 4 lines (the 4 store#s). 
<a href="https://filedb.experts-exchange.com/incoming/2017/03_w12/1152579/2017-03-23_10-14-38.png" target="_blank" class="file-inline" title="2017-03-23_10-14-38.png (38 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>2017-03-23_10-14-38.png</a> 
<a href="https://filedb.experts-exchange.com/incoming/2017/03_w12/1152580/2017-03-23_10-15-39.png" target="_blank" class="file-inline" title="2017-03-23_10-15-39.png (38 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>2017-03-23_10-15-39.png</a> 
<a href="https://filedb.experts-exchange.com/incoming/2017/03_w12/1152581/2017-03-23_10-16-35.png" target="_blank" class="file-inline" title="2017-03-23_10-16-35.png (23 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>2017-03-23_10-16-35.png</a> 
<a href="https://filedb.experts-exchange.com/incoming/2017/03_w12/1152582/2017-03-23_10-11-53.png" target="_blank" class="file-inline" title="2017-03-23_10-11-53.png (4 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>2017-03-23_10-11-53.png</a>
</div>

2017-03-23_10-15-39.png

2017-03-23_10-14-38.png

2017-03-23_10-16-35.png

2017-03-23_10-11-53.png

<div>
Ok. &nbsp;Something in building the row data isn't behaving as expected. &nbsp;(Probably another data issue.) 
 
What do we get from this: 
 

<pre><code class="code-10 nolinks" id="code-20-42061957-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;
</code></pre>
</div>

<div>
When I put 'Select * from Heading' after the first part of the Heading query I get the attached results. 
 
... 
Heading (tn, text) as 
( 
&nbsp; SELECT 1, cast('Store#' as varchar (400)) text from sysibm.sysdummy1 
&nbsp; UNION ALL 
&nbsp; SELECT tn+1, text || ', ' || csg 
&nbsp; FROM csg_list, Heading 
&nbsp; WHERE tn = rn 
) 
Select * from Heading 
<a href="https://filedb.experts-exchange.com/incoming/2017/03_w12/1152586/2017-03-23_10-31-00.png" target="_blank" class="file-inline" title="2017-03-23_10-31-00.png (99 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>2017-03-23_10-31-00.png</a>
</div>

2017-03-23_10-31-00.png

<div>
That looks normal. &nbsp;The recursive query builds up the row by appending the text to the previously created row. &nbsp;That's why the final query selects the row with the highest ID value, to get only the row with all of the data. 
 
The missing data probably comes from something in the Results sub-query though.
</div>

<div>
It looks like I didn't carry forward a change to the csg_list sub-query. &nbsp;Let's go back to a baseline. 
 

<pre><code class="code-10 nolinks" id="code-20-42061989-1">WITH csg_list (rn, csg) as
(
 SELECT row_number () over (order by csg) rn, csg
 FROM (SELECT distinct csg FROM a WHERE csg &lt;&gt; ' ' 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;
</code></pre>
</div>

<div>
From your above question, I get the attached results. 
<a href="https://filedb.experts-exchange.com/incoming/2017/03_w12/1152589/2017-03-23_10-41-26.png" target="_blank" class="file-inline" title="2017-03-23_10-41-26.png (63 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>2017-03-23_10-41-26.png</a> 
<a href="https://filedb.experts-exchange.com/incoming/2017/03_w12/1152590/2017-03-23_10-41-53.png" target="_blank" class="file-inline" title="2017-03-23_10-41-53.png (87 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>2017-03-23_10-41-53.png</a> 
<a href="https://filedb.experts-exchange.com/incoming/2017/03_w12/1152591/2017-03-23_10-42-16.png" target="_blank" class="file-inline" title="2017-03-23_10-42-16.png (75 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>2017-03-23_10-42-16.png</a>
</div>

2017-03-23_10-41-53.png

2017-03-23_10-42-16.png

2017-03-23_10-41-26.png

<div>
From the query in your response 'It looks like I didn't carry forward a change to the csg_list sub-query. &nbsp;Let's go back to a baseline.', I get the attached results. 
<a href="https://filedb.experts-exchange.com/incoming/2017/03_w12/1152593/2017-03-23_10-51-27.png" target="_blank" class="file-inline" title="2017-03-23_10-51-27.png (10 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>2017-03-23_10-51-27.png</a>
</div>

2017-03-23_10-51-27.png

<div>
Man, but I need to apologize. &nbsp;I copied the wrong thing. &nbsp;:( &nbsp;One more time... 
 

<pre><code class="code-10 nolinks" id="code-20-42062050-1">WITH csg_list (rn, csg) as
(
 SELECT row_number () over (order by csg) rn, csg
 FROM (SELECT distinct csg FROM a WHERE csg &lt;&gt; ' ' 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);
</code></pre>
</div>

<div>
Looking better. Attached are the results. Can I get it in columns and rows like the 2nd attachment so that the order counts will be below the correct CSG's? 
 
Thank you, 
 
James 
<a href="https://filedb.experts-exchange.com/incoming/2017/03_w12/1152604/2017-03-23_11-30-56.png" target="_blank" class="file-inline" title="2017-03-23_11-30-56.png (18 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>2017-03-23_11-30-56.png</a> 
<a href="https://filedb.experts-exchange.com/incoming/2017/03_w12/1152608/2017-03-23_11-34-30.png" target="_blank" class="file-inline" title="2017-03-23_11-34-30.png (18 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>2017-03-23_11-34-30.png</a>
</div>

2017-03-23_11-34-30.png

2017-03-23_11-30-56.png

<div>
Ok. &nbsp;This looks pretty good to me. &nbsp;:) 
 

<pre><code class="code-10 nolinks" id="code-20-42062229-1">WITH csg_list (rn, csg) as
(
 SELECT row_number () over (order by csg) rn, csg
 FROM (SELECT distinct csg FROM a WHERE csg &lt;&gt; ' ' 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);
</code></pre>
</div>

<div>
Attached are the results. Can I get it in columns and rows like the 2nd attachment so that the order counts will be aligned below the corresponding CSG's? 
 
Thanks, 
 
James 
<a href="https://filedb.experts-exchange.com/incoming/2017/03_w12/1152633/2017-03-23_13-32-31.png" target="_blank" class="file-inline" title="2017-03-23_13-32-31.png (21 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>2017-03-23_13-32-31.png</a> 
<a href="https://filedb.experts-exchange.com/incoming/2017/03_w12/1152636/2017-03-23_11-34-30.png" target="_blank" class="file-inline" title="2017-03-23_11-34-30.png (18 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>2017-03-23_11-34-30.png</a>
</div>

2017-03-23_13-32-31.png

<div>
That's a bit trickier, but doable. &nbsp;:) &nbsp;I'll get it done. 
 
The comma separated results are intended for opening/importing into Excel. 
 
Kent
</div>

<div>
Kent, 
 
How could I import the results into Excel and have the results aligned under the corresponding column headers (CSG's)?
</div>

<div>
Save the results in a file (on a PC, not the AS400) with a file extension of .csv. &nbsp;Then open the file with excel. 
 
You can also copy and paste the contents into Excel, but they'll probably all wind up in column A. &nbsp;If so, click on the Data tab and then click on the &quot;Text To Columns&quot; icon. 
 
Either should work just fine. 
 
Kent
</div>

<div>
Excel also has a Pivot function. &nbsp;A much simpler query with the CSG headings down the left side and the store numbers across the top is available to us. &nbsp;Once imported into Excel, Click on the Insert tab (icon), then on Pivot Table. &nbsp;Excel should walk you through pivoting the data so that the store numbers are on the left and the CSG values along the top.
</div>

<div>
The problem I'm having with it is that the data is not aligned under the corresponding column header (CSG). When I put it into Excel, it all goes into column A as you said. When I select column A and then click on the Data tab and then click on the &quot;Text To Columns&quot; icon, and I convert the text to columns using commas as the delimiter, the data is separated in columns from left to right but not aligned in the correct cells under the corresponding column headers (CSG's). The data is just filled into the cells from left to right. Hope that makes sense. 
 
Thanks, 
 
James
</div>

<div>
It does make sense. &nbsp; 
 
Let me check the results and make sure that they line up in the text we're producing.
</div>

<div>
Not every store has an order for each CSG. Maybe we could put zeros in where there's no orders?
</div>

<div>
Using COALESCE() to put zeros into the sums in place of NUL values is a good idea when possible.
</div>

<div>
Hi James, 
 
Using my (admittedly limited) sample data everything lines up fine. &nbsp;And I see zeroes (not NULL or blank). 
 
When you select &quot;Text to Columns&quot;, make sure you also select &quot;delimited&quot; and that the comma is the delimiter. 
 
Also, when I save the output to a file as &quot;sample.csv&quot; and open it with Excel it also formats correctly. 
 
Kent
</div>

<div>
Kent, 
 
Thank you. I did get it to format correctly in Excel. I am working with QMF and trying to get the results to export into Excel with a procedure like many of my other queries. Is there a way to have the data display in QMF like one of the examples in the attached file so that it doesn't need manual formatting in Excel? 
 
Thanks again, 
 
James
</div>

<div>
Attached are how the results currently look in QMF 
<a href="https://filedb.experts-exchange.com/incoming/2017/03_w12/1152788/2017-03-24_7-48-53.png" target="_blank" class="file-inline" title="2017-03-24_7-48-53.png (21 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>2017-03-24_7-48-53.png</a>
</div>

2017-03-24_7-48-53.png

<div>
Attached are the results from your above query. 
<a href="https://filedb.experts-exchange.com/incoming/2017/03_w12/1152858/2017-03-24_11-27-43.png" target="_blank" class="file-inline" title="2017-03-24_11-27-43.png (21 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>2017-03-24_11-27-43.png</a>
</div>

2017-03-24_11-27-43.png

<div>
Let's see if Tom (tliotta) is still lurking here. 
 
When I run that on my version of DB2, I get the expected results (below). &nbsp;Of course, I don't have QMF available to me. &nbsp;I suspect that QMF is compacting consecutive spaces, but I can't test or prove that. 
 
Tom. &nbsp;Do you have an idea of what's happening or what our next step should be? 
 
Thanks, 
Kent 
 

<pre><code class="code-1 nolinks" id="code-20-42063713-1">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
</code></pre>
</div>

<div>
Ok, thank you very much Kent
</div>

<div>
Also no &quot;QMF&quot; here, but I'll set up DB2 with test data above (this evening perhaps) and see what happens in a couple different clients. I see at least one change in test data that could help narrow down any issue. I'll post back when I know something.
</div>

<div>
No good news from me. Exact match with Kent's results. I also made a minor change to Kent's test data to verify an odd possibility, but the result was still perfect. 
 
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.
</div>

<div>
Sorry, I've been on vacation. I will be posting​ the query soon. 
 
Thanks again
</div>

<div>
Here is my query: 
 
WITH consol_sel_grp_list (rn, csg) as 
( 
&nbsp; SELECT row_number () over (order by csg) rn, 
csg 
&nbsp; FROM (SELECT distinct csg FROM ddtcenvr.mrr 
WHERE csg &lt;&gt;&nbsp;' ' AND csg is not NULL 
and doc_id = 'A5J') t3 
), 
Store_Summary (rn, store, csg, count) 
as 
( 
&nbsp; SELECT row_number () over (partition by t0.store 
order by t0.store, t1.csg) RN, t0.store, 
t1.csg, t2.count 
&nbsp; FROM (select distinct store from ddtcenvr.mrr 
where doc_id = 'A5J') t0 cross join csg_list t1 
&nbsp; LEFT JOIN (SELECT store, csg, 
count(ordernumber) count FROM ddtcenvr.mrr where doc_id = 'A5J' 
group by store, csg) t2 
&nbsp; &nbsp; ON t0.store = t2.store 
&nbsp; &nbsp;AND t1.csg = t2.csg 
), 
Results (ln, store, total, text) as 
( 
&nbsp; 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 
&nbsp; UNION ALL 
&nbsp; SELECT ln+1, c.store, Total+coalesce(count,0), text || ' | ' || 
right (' &nbsp; &nbsp; &nbsp; ' || cast (coalesce (count,0) as varchar(8)), 8) 
&nbsp; FROM Results c, Store_Summary b 
&nbsp; WHERE &nbsp;c.store = b.store 
&nbsp; &nbsp; AND c.ln = b.rn 
), 
Footing (ln, text) as 
( 
&nbsp; SELECT 0 ln, cast (cast ('Totals' as char(10)) as varchar (400)) 
FROM sysibm.sysdummy1 
&nbsp; UNION ALL 
&nbsp; SELECT ln+1, Text || ' | ' || right (' &nbsp; &nbsp; &nbsp; ' || 
cast ((SELECT coalesce (SUM (count), 0) 
FROM Store_Summary SS 
WHERE SS.csg = csg_list.csg) 
as varchar (8)), 8) 
&nbsp; FROM csg_list, Footing 
&nbsp; WHERE rn = ln+1 
), 
Heading (tn, text) as 
( 
&nbsp; SELECT 1, cast(cast ('Store#' as char(10)) 
as varchar (400)) text 
from sysibm.sysdummy1 
&nbsp; UNION ALL 
&nbsp; SELECT tn+1, text || ' | ' || cast (csg as char(8)) 
&nbsp; FROM csg_list, Heading 
&nbsp; WHERE tn = rn 
) 
SELECT text || ' | Total' 
FROM Heading 
WHERE Heading.tn = (SELECT count(*) from csg_list) + 1 
UNION ALL 
SELECT Text || ' | ' || right (' &nbsp; &nbsp; &nbsp; ' || 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 (' &nbsp; &nbsp; &nbsp; ' || cast ((SELECT sum(count) 
FROM store_Summary) as varchar(10)), 8) 
FROM Footing F 
WHERE f.ln = (SELECT max(ln) FROM footing) 
 
 
Thanks everyone, 
 
James
</div>

<div>
I will see if I can try it out on another client. 
 
Thanks everyone
</div>

<div>
<div class="content wysiwyg-content">
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
</div>
</div>

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

Crosstab query for QMF/DB2

IBM DB2 is a family of relational database server products developed by IBM that have been extended to support object-relational features and non-relational structures like JSON and XML. There are three main products in the DB2 family: DB2 for Linux, UNIX and Windows (informally known as DB2 LUW), DB2 for z/OS (mainframe), and DB2 for i (formerly OS/400), plus a version for IBM's Virtual Storage Extended (VSE) operating system.