Combining Columns for Cross-Tab (Fixed Diagrams)

[r0brh0des]

I'm trying to combine code values from different columns into one column to use in a crosstab report....concatinating comes close but I need the values on seperate rows not together on one.

data desired concat
key a b key a b cmb key a b cmb

123 w x 123 w x w 123 w x wx
123 y z 123 w x x 123 w x wx
123 y z y 123 y z yz
123 y z z 123 y z yz

? select a+b as cmb
from table

Many thanks in advance,
Rob

 

[r937]

can you please explain in words what you're looking for?

i can't figure out the "desired" column

r937.com | rudy.ca

 

[r0brh0des]

I have to write this crosstab report for an educational institution and it uses one table but the way the data needs to be presented as I stand now I have to combine 7 cross-tabs and paste them into excel.

The table has columns like classLevel(fresh, soph, sen, jun), collegeAttendStatus (new, continuing, returning) and fullPartTime(full, part).

I would need 2 cross tabs (classLevel/collegeAttend and classLevel/full_part) to get a count of say how many seniors are new, cont, return, fullTime, PartTime.

If I could combine the values for fullPart with collegeAttend into a single column in a view I could run one cross tab(classLevel/combinedColumn)

In the diagrams 'desired' and 'concat' are result tables from queries 'desired' being the unknown at this point.

'data' is the example table being queried on.

Hope this makes a little more sense.

Thanks!

 

[r937]

even less

maybe someone else can help



r937.com | rudy.ca

 

[PHV]

What about an UNION query ?
SELECT key, a, b, a cmb FROM yourTable
UNION SELECT key, a, b, b FROM yourTable

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[r0brh0des]

Ah.....the infamous union.....that did it!

Thanks!