Combine multiple rows into single row with sql

  • Thread starter Thread starter Rajesh B. Patel
  • Start date Start date
R

Rajesh B. Patel

hello,

I see many posts on this topic, but still am not sure on the simplest proper
solution.

I have a table

ID DOB
1 9/1/77
2 11/30/77
3 6/23/77

and another table

ID ExamNumber Date
1 1 11/1/90
1 2 1/1/97
1 3 2/1/96
2 1 3/1/94
2 2 4/1/89
2 3 5/1/84
3 1 6/1/85
3 2 7/1/95
3 3 8/1/95

I want a sql query that will produce

ID DOB Date 1 Date 2 Date 3
1 9/1/77 11/1/90 1/1/97 2/1/96
2 11/30/77 ....

How can I do this with straight sql? I have a crosstab as follows that seems
to work as follows -

TRANSFORM First(qryTest.Date) AS FirstOfDate SELECT qryTest.ParticipantID,
qryTest.DOB, First(qryTest.Date) AS [Total Of Date] FROM qryTest GROUP BY
qryTest.ParticipantID, qryTest.DOB PIVOT qryTest.ExamNumber;

Is this guaranteed to give me the results I desire. The part I question is
First(qryTest.Date) since I just want to display all the Date for a
particular ID/DOB combo...

Thanks in advance

raj
 
Hi,


All depends of qryTest, a query, so, of its SQL formulation.

For the actual crosstab, it requires an Aggregate. When none really
matters, because there is "just one" record per group (under a pivot) in
any case, you can use MIN, MAX, FIRST or LAST, even SUM or AVG (average) if
the data is numerical, ... they all produce the same result. FIRST, being
the value of the field from the first record *seen* in the group, not the
EARLIEST (which would be MIN(over a date) ), is just a convenient aggregate.


Hoping it may help,
Vanderghast, Access MVP
 
Hi,

This does help in the sense that it appears I will always get the right
result since there is only one date associated with each
ExamNumber/Particpant combination. My other question is this possible with
some other type of query? Not a crosstab? Thanks.

raj

Michel Walsh said:
Hi,


All depends of qryTest, a query, so, of its SQL formulation.

For the actual crosstab, it requires an Aggregate. When none really
matters, because there is "just one" record per group (under a pivot) in
any case, you can use MIN, MAX, FIRST or LAST, even SUM or AVG (average) if
the data is numerical, ... they all produce the same result. FIRST, being
the value of the field from the first record *seen* in the group, not the
EARLIEST (which would be MIN(over a date) ), is just a convenient aggregate.


Hoping it may help,
Vanderghast, Access MVP



Rajesh B. Patel said:
hello,

I see many posts on this topic, but still am not sure on the simplest proper
solution.

I have a table

ID DOB
1 9/1/77
2 11/30/77
3 6/23/77

and another table

ID ExamNumber Date
1 1 11/1/90
1 2 1/1/97
1 3 2/1/96
2 1 3/1/94
2 2 4/1/89
2 3 5/1/84
3 1 6/1/85
3 2 7/1/95
3 3 8/1/95

I want a sql query that will produce

ID DOB Date 1 Date 2 Date 3
1 9/1/77 11/1/90 1/1/97 2/1/96
2 11/30/77 ....

How can I do this with straight sql? I have a crosstab as follows that seems
to work as follows -

TRANSFORM First(qryTest.Date) AS FirstOfDate SELECT qryTest.ParticipantID,
qryTest.DOB, First(qryTest.Date) AS [Total Of Date] FROM qryTest GROUP BY
qryTest.ParticipantID, qryTest.DOB PIVOT qryTest.ExamNumber;

Is this guaranteed to give me the results I desire. The part I question is
First(qryTest.Date) since I just want to display all the Date for a
particular ID/DOB combo...

Thanks in advance

raj
Click to expand...
Click to expand...
 
Hi,


Well, if you make a total query with group on the primary key, you
would end up with exactly the same pattern. It is also possible in a
"lookup" like:

.... WHERE fieldName = ( SELECT f1 FROM tablename WHERE
someRestrictiveCondition )

would work as long as there is just one record returned in the sub-select
query, otherwise, someone has to use IN, or an aggregate, or a universal
qualifier. To illustrate that last part, which is not directly related with
your initial question but may be of some interest:


.... WHERE fieldName IN ( SELECT f1 FROM tablename )

is a possible formulation if fieldName has to be among the values supplied
for f1 in the mentioned table;

.... WHERE fieldDateTime = (SELECT MIN(dateTime) FROM tableName WHERE ... )

is possible to be used if you "look up" for the first occurrence of an
event, as example. The aggregate MIN insure us that there would be at MOST
just ONE value returned by the sub-select query, so = can be safely used,
rather than IN( ). An example about using an universal qualifier:

.... WHERE True = ALL ( SELECT prerequisite FROM tableName WHERE ... )

if you look for conditions where all the relevant prerequisites are reported
to be true.




Hoping it may help,
Vanderghast, Access MVP



Rajesh B. Patel said:
Hi,

This does help in the sense that it appears I will always get the right
result since there is only one date associated with each
ExamNumber/Particpant combination. My other question is this possible with
some other type of query? Not a crosstab? Thanks.

raj

Michel Walsh said:
Hi,


All depends of qryTest, a query, so, of its SQL formulation.

For the actual crosstab, it requires an Aggregate. When none really
matters, because there is "just one" record per group (under a pivot) in
any case, you can use MIN, MAX, FIRST or LAST, even SUM or AVG (average) if
the data is numerical, ... they all produce the same result. FIRST, being
the value of the field from the first record *seen* in the group, not the
EARLIEST (which would be MIN(over a date) ), is just a convenient aggregate.


Hoping it may help,
Vanderghast, Access MVP



Rajesh B. Patel said:
hello,

I see many posts on this topic, but still am not sure on the simplest proper
solution.

I have a table

ID DOB
1 9/1/77
2 11/30/77
3 6/23/77

and another table

ID ExamNumber Date
1 1 11/1/90
1 2 1/1/97
1 3 2/1/96
2 1 3/1/94
2 2 4/1/89
2 3 5/1/84
3 1 6/1/85
3 2 7/1/95
3 3 8/1/95

I want a sql query that will produce

ID DOB Date 1 Date 2 Date 3
1 9/1/77 11/1/90 1/1/97 2/1/96
2 11/30/77 ....

How can I do this with straight sql? I have a crosstab as follows that seems
to work as follows -

TRANSFORM First(qryTest.Date) AS FirstOfDate SELECT qryTest.ParticipantID,
qryTest.DOB, First(qryTest.Date) AS [Total Of Date] FROM qryTest GROUP BY
qryTest.ParticipantID, qryTest.DOB PIVOT qryTest.ExamNumber;

Is this guaranteed to give me the results I desire. The part I
Click to expand...
Click to expand...
question
is
Click to expand...
Click to expand...
 
Back
Top