Query results in a "table"

Transact-SQL
1

Hi all....been trying to "learn myself" some SQL...I am able to run basic and moderate stuff, but normally rely on Excel to massage data into final format and calculations.
I am sure you could all do this in your sleep, but it is driving me crazy.....
I have a table (REPS) contain reps info, including their IDNumber and region.
i can get the results like so:

but I am trying to get the results to appear with the region as column headers and the reps below in the column:

Any help of insight would be appreciated....

BTW....using QMF for Windows V8 (yeah - that is what the company provided)

2

OK.....I seem to be making some progress...

SELECT
NUMBER
, (CASE REGION WHEN '1' THEN 1 ELSE 0 END) AS REG1
, (CASE REGION WHEN '2' THEN 1 ELSE 0 END) AS REG2
, (CASE REGION WHEN '3' THEN 1 ELSE 0 END) AS REG3
, (CASE REGION WHEN '4' THEN 1 ELSE 0 END) AS REG4
FROM
REPS
WHERE STATUS = 'A'
AND NOT (NUMBER = SUPV)
GROUP BY REGION, NUMBER
ORDER BY REGION, NUMBER

and this returns :

Pasted image214×981 4.64 KB

but how do i get the NUMBER into the column where the "1" is??

3

People are reluctant to respond probably because you are using DB2/QMF, and this forum is a dedicated Microsoft SQL Server forum. So expertise on QMF is likely to be rare, if at all among the people who hang around on this forum.

4

Maybe this:

with cte(region,number,rn)
  as (select region
            ,number
            ,row_number() over(partition by region order by number) as rn
        from reps
       where status='A'
         and number<>supv
     )
select max(case when b.region=1 then b.number else null end) as reg1
      ,max(case when b.region=2 then b.number else null end) as reg2
      ,max(case when b.region=3 then b.number else null end) as reg3
      ,max(case when b.region=4 then b.number else null end) as reg4
  from (select rn
          from cte
         group by rn
       ) as a
       left outer join cte as b
                    on b.rn=a.rn
 group by a.rn
 order by a.rn
;

Edit: Forgot to implement your where statement