convert rows to columns

[techieWannabe]

Hi,

I want to convert rows that have the same ID but additional data to one single row where the additional data would go to new columns.

For example:

ID Name Rate DistrPerc
-----------------------------------------
111 Amy 25000.00 5
111 Amy 30000.00 6
222 Bob 28000.00 2

and change it so it would look like this...

ID Name Rate1 DistrPerc1 Rate2 DistriPerc
-----------------------------------------------------------
111 Amy 25000.00 5 30000.00 6
222 Bob 28000.00 2

 

[dickiebird]

This gets asks lots of times in various DB/SQL forums - see thread183-459197 for starters, or do an advanced search for the exact phrase 'rows to columns'
HTH Dickie Bird (-)))

 

[techieWannabe]

Thanks, but I am using MS Access, would that be different?

 

[swampBoogie]

Look up the transform statement in your docs.

 

[dannywild82]

no no no no no!!!!

you should NORMALISE YOUR DATA! then you wouldnt have to do this anyway!

if youre looking for output use a report wizard

 

[techieWannabe]

Could you give me more information about what NORMALISE is? I've tried to search it here, but didn't find help much. Thanks.

 

[DaPi]

"Normalisation" is a kind of standard for clean db design - I'm sure there are whole books on it. More than the pathetic few pages from the begining of the Access manual. Sorry I don't have a sugestion/reference . . .

My reaction to these questions is - if you want a layout inspired by Excel, use Excel! Should be easy to dump your data into Excel and make a pivot table or what ever. DaPi - no silver bullet

 

[dannywild82]

there are various forms of normal(ity)

UN-NORMALISED (you)
1NF (no vector elements)
2NF where the primary key is of more than one attrib, no non-key attrib functionaly dependant on PART of that key
3NF - no non key attrib f.d. on any other non key attrib

( i think they are in the right order)

there should be a tutorial somewhere. normalisation is more of a prosess rather than a no-brainer. - dont bother with a quick fix. head in book all day breeds good results

 

[dannywild82]

apoligez for da spellink - dont bother with a quick fix. head in book all day breeds good results