Build new row, consolidating columns...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have table as such:

Dept Category Amount
1 A 50
1 b 25
1 c 15
2 A 10

Would like to create 1 row for Dept for Report purposes
Dept Cat1 Cat1Amt Cat2 Cat2Amt.....etc
1 A 50 b 25

Basically I need for a report..unless way to query/self join to get same
result..?

Thanks!
 
Dear Don:

There are two approaches. One is quite sophisticated, requiring pretty fair
programming skills. The other is simpler, but not as flexible.

The simple approach depends on there being a small, fixed set of categories.
It won't work if new categories are added. It goes like this:

SELECT Dept, Category AS CatA, Amount AS CatAAmt,
NULL AS CatB, NULL AS CatBAmt,
NULL AS CatC, NULL AS CatCAmt,
NULL AS CatD, NULL AS CatDAmt,
NULL AS CatE, NULL AS CatEAmt,
NULL AS CatF, NULL AS CatFAmt
FROM YourTable
WHERE Category = "A"
UNION ALL
SELECT Dept, NULL AS CatA, NULL AS CatAAmt,
Category AS CatB, Amount AS CatBAmt,
NULL AS CatC, NULL AS CatCAmt,
NULL AS CatD, NULL AS CatDAmt,
NULL AS CatE, NULL AS CatEAmt,
NULL AS CatF, NULL AS CatFAmt
FROM YourTable
WHERE Category = "B"
UNION ALL
SELECT Dept, NULL AS CatA, NULL AS CatAAmt,
NULL AS CatB, NULL AS CatBAmt,
Category AS CatC, Amount AS CatCAmt,
NULL AS CatD, NULL AS CatDAmt,
NULL AS CatE, NULL AS CatEAmt,
NULL AS CatF, NULL AS CatFAmt
FROM YourTable
WHERE Category = "C"
UNION ALL
SELECT Dept, NULL AS CatA, NULL AS CatAAmt,
NULL AS CatB, NULL AS CatBAmt,
NULL AS CatC, NULL AS CatCAmt,
Category AS CatD, Amount AS CatDAmt,
NULL AS CatE, NULL AS CatEAmt,
NULL AS CatF, NULL AS CatFAmt
FROM YourTable
WHERE Category = "D"
UNION ALL
SELECT Dept, NULL AS CatA, NULL AS CatAAmt,
NULL AS CatB, NULL AS CatBAmt,
NULL AS CatC, NULL AS CatCAmt,
NULL AS CatD, NULL AS CatDAmt,
Category AS CatE, Amount AS CatEAmt,
NULL AS CatF, NULL AS CatFAmt
FROM YourTable
WHERE Category = "E"
UNION ALL
SELECT Dept, NULL AS CatA, NULL AS CatAAmt,
NULL AS CatB, NULL AS CatBAmt,
NULL AS CatC, NULL AS CatCAmt,
NULL AS CatD, NULL AS CatDAmt,
NULL AS CatE, NULL AS CatEAmt,
Category AS CatF, Amount AS CatFAmt
FROM YourTable
WHERE Category = "F"

Save this as query. I'll assume the saved name of this is MyUnion.

Next:

SELECT Dept, MAX(CatA) AS CatA, MAX(CatAAmt) AS CatAAmt,
MAX(CatA) AS CatA, MAX(CatAAmt) AS CatBAmt,
MAX(CatA) AS CatA, MAX(CatAAmt) AS CatCAmt,
MAX(CatA) AS CatA, MAX(CatAAmt) AS CatDAmt,
MAX(CatA) AS CatA, MAX(CatAAmt) AS CatEAmt,
MAX(CatA) AS CatA, MAX(CatAAmt) AS CatFAmt
FROM MyUnion
GROUP BY Dept

Is this the appearance you wanted.

And that's the "easy way"!

Tom Ellison
 
I would use a main report based on distinct Dept values. Then add a multiple
column subreport that displays across then down to display the Category &
Amount fields.
 
Back
Top