CODEWITHSUNDEEP
sqlms-access-2007transform
Omar Rehman
Omar Rehman

Reputation: 1

MS Access 2007: Using SQL to Transpose rows to columns

SQL newbie here. I have table setup as the following (my apologies for the poor formatting):

Cycle ID....UBCI....chemo_cycle....csf....rsn_no_csf
57.............18001...1......................N.......N
58.............18001...2......................N.......N
59.............22002...1......................Y........null

I'd like to convert it into the following format:

UBCI....chemo_cycle1....chemo_cycle2....csf1....csf2....rsn_no_csf1....rsn_no_csf2
18001...1.........................2.........................N........N........N.....................N
22002...1.........................null......................Y........null.....null..................null

There can be up to 26 chemo_cycles per UBCI. I tried some of the other suggestions, but wasn't familiar enough with SQL to work out some of the finer details. Again, apologies for the formatting. Any help would be appreciated.

Thank you!

-Omar

Upvotes: 0

Views: 3660

Answers (1)

Fionnuala
Fionnuala

Reputation: 91326

The only way I can see to do this conveniently in Access is with four queries. I do not think you can use crosstab queries as subqueries, so:

Query 1: cc

TRANSFORM First(TableQ.chemo_cycle) AS FirstOfchemo_cycle
SELECT TableQ.UBCI
FROM TableQ
GROUP BY TableQ.UBCI
PIVOT "CC " & [chemo_cycle];

Query 2: csf

TRANSFORM First(TableQ.csf) AS FirstOfcsf
SELECT TableQ.UBCI
FROM TableQ
GROUP BY TableQ.UBCI
PIVOT "csf " & [chemo_cycle];

Query 3: rsn

TRANSFORM First(TableQ.rsn_no_csf) AS FirstOfrsn_no_csf
SELECT TableQ.UBCI
FROM TableQ
GROUP BY TableQ.UBCI
PIVOT "rsn " & [chemo_cycle];

Final query: The columns [cc 1] to [cc 26] will show in the design window, and can be added, here they are indicated by <...>

SELECT CC.UBCI, 
       CC.[CC 1], 
       CC.[CC 2], 
       <...>
       csf.[csf 1], 
       csf.[csf 2], 
       <...>
       rsn.[rsn 1], 
       rsn.[rsn 2]
       <...>
FROM (CC INNER JOIN csf 
ON CC.UBCI = csf.UBCI) 
INNER JOIN rsn 
ON csf.UBCI = rsn.UBCI;

Upvotes: 1

Related Questions