tahirih
asked on
Access - Transpose Data Rows
In the following example, I will be adding the first three columns to an already existing table:
RecordID Line No PersonID Field1 Field2 Field3 Field4
1 1 1 John Smith 2009 Pass 12/2008
2 2 1 Biochemisty MD Rockvillle 20852
3 3 1 Honors
There will be multiple individuals with this format. Not all individuals will have all Line No 1-3 (i.e. a person can have 1, 2, or 3, lines).
The end result will be one table, where each individual (Columns 1 - 3 all equal 1), will transpose the bottom one or two rows (Line No 2 and 3), into Line No 1.
I have not been successful in my initial attempts, in A. auto numbering the first three columns depending on how many rows the individial has (between 1 and 3), and then transposing all rows to one row for the individual.
Assistance with SQL coding is appreciated. Thank you
RecordID Line No PersonID Field1 Field2 Field3 Field4
1 1 1 John Smith 2009 Pass 12/2008
2 2 1 Biochemisty MD Rockvillle 20852
3 3 1 Honors
There will be multiple individuals with this format. Not all individuals will have all Line No 1-3 (i.e. a person can have 1, 2, or 3, lines).
The end result will be one table, where each individual (Columns 1 - 3 all equal 1), will transpose the bottom one or two rows (Line No 2 and 3), into Line No 1.
I have not been successful in my initial attempts, in A. auto numbering the first three columns depending on how many rows the individial has (between 1 and 3), and then transposing all rows to one row for the individual.
Assistance with SQL coding is appreciated. Thank you
dqmq
Considering only the columns that already exist, how do you know which lines 2 and 3 go with which line 1? In your sample, no relationship between John Smith and Biochemistry is apparent.
As I'm waiting for an answer, I anticipate you will tell me the rows are in order, i.e. John Smiths rows are grouped together followed by the next person. If that's the case, and that's all we have, you are out of luck, my friend.
Waiting anxiously...
Waiting anxiously...
ASKER
Yes, this is what we have. All I can offer is that each individual has between 1 and 3 rows, in consequential manner (i.e. All of John Smith's rows, then all of Jane's, etc.)
Can you think of a way to combine up to three rows into one per person. I have not been able to program this in SQL.
Thanks.
Can you think of a way to combine up to three rows into one per person. I have not been able to program this in SQL.
Thanks.
post the expected outcome
ASKER
This is what I would want to see, per individual (we have highlighted only one individual:
John Smith 2009 Pass 12/2008 Biochemisty MD Rockvillle 20852 Honors
Please note that from "Biochemistry" to "Honors, we would be creating new fields (Fields 5 - 9)
Thanks
John Smith 2009 Pass 12/2008 Biochemisty MD Rockvillle 20852 Honors
Please note that from "Biochemistry" to "Honors, we would be creating new fields (Fields 5 - 9)
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>Can you think of a way to combine up to three rows into one per person. I have not been able to program this in SQL.
If you populate LineNo and PersonID manually, then it goes like this:
Select
O1.PersonID as PersonID
,O1.Field1 as Name
,O1.Field2 as Year
,O1.Field3 as Grade
,O1.Field4 as Whatever
,O2.Field1 as Major
,O2.Field2 as Degree
...
,O3.Field4 as ?????
into NEWTABLE
FROM
OLDTABLE O1
left join OLDTABLE O2 on O1.personID = O2.PersonID and O1.LineNo = 1 and O2.LineNo = 2
left join OLDTABLE O3 on O3.personID = O1.PersonID and O1.LineNo = 1 and O3.LineNo = 3
If you populate LineNo and PersonID manually, then it goes like this:
Select
O1.PersonID as PersonID
,O1.Field1 as Name
,O1.Field2 as Year
,O1.Field3 as Grade
,O1.Field4 as Whatever
,O2.Field1 as Major
,O2.Field2 as Degree
...
,O3.Field4 as ?????
into NEWTABLE
FROM
OLDTABLE O1
left join OLDTABLE O2 on O1.personID = O2.PersonID and O1.LineNo = 1 and O2.LineNo = 2
left join OLDTABLE O3 on O3.personID = O1.PersonID and O1.LineNo = 1 and O3.LineNo = 3
ASKER
Please note that I will not be manually populating the preceeding three ID columns. This would be automated - and the catch here is not all individuals will have all three rows.
>Please note that I will not be manually populating the preceeding three ID columns. This would be automated - and the catch here is not all individuals will have all three rows.
I don't care how they get populated. Once populated, then my SQL combines 1 to 3 rows into a single row. The only requirement are that every Person has LineNo=1.
Unfortunately, there is no way to populate the first 3 columns automatically with the data you have supplied. Good Luck.
I don't care how they get populated. Once populated, then my SQL combines 1 to 3 rows into a single row. The only requirement are that every Person has LineNo=1.
Unfortunately, there is no way to populate the first 3 columns automatically with the data you have supplied. Good Luck.
Oh...since this is Access you may need parens:
Select
O1.PersonID as PersonID
,O1.Field1 as Name
,O1.Field2 as Year
,O1.Field3 as Grade
,O1.Field4 as Whatever
,O2.Field1 as Major
,O2.Field2 as Degree
...
,O3.Field4 as ?????
into NEWTABLE
FROM
((OLDTABLE O1
left join OLDTABLE O2 on O1.personID = O2.PersonID and O1.LineNo = 1 and O2.LineNo = 2)
left join OLDTABLE O3 on O3.personID = O1.PersonID and O1.LineNo = 1 and O3.LineNo = 3)
Select
O1.PersonID as PersonID
,O1.Field1 as Name
,O1.Field2 as Year
,O1.Field3 as Grade
,O1.Field4 as Whatever
,O2.Field1 as Major
,O2.Field2 as Degree
...
,O3.Field4 as ?????
into NEWTABLE
FROM
((OLDTABLE O1
left join OLDTABLE O2 on O1.personID = O2.PersonID and O1.LineNo = 1 and O2.LineNo = 2)
left join OLDTABLE O3 on O3.personID = O1.PersonID and O1.LineNo = 1 and O3.LineNo = 3)
ASKER
Please find attached an example table.
Also, note that I have purposely indicated that Jane S. does not have a third row. In the "New" table, the first three identifying columns are removed.
Hope this helps.
Thanks
Sample.xls
Also, note that I have purposely indicated that Jane S. does not have a third row. In the "New" table, the first three identifying columns are removed.
Hope this helps.
Thanks
Sample.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.