shacho
asked on
Merging data into single record
I have data that looks like this:
id name_type name_value
1 first bob
1 middle joe
1 last smith
I want it to look like this:
id first middle last
1 bob joe smith
I can't do it with crosstab since you can't aggregate strings.
I created a query that does this:
id first middle last
1 bob [null] [null]
1 [null] joe [null]
1 [null] [null] smith
thinking I could use group by to coalesce them into a single record, but that didn't work.
Not sure what to do unless I try it with Domain functions,
id name_type name_value
1 first bob
1 middle joe
1 last smith
I want it to look like this:
id first middle last
1 bob joe smith
I can't do it with crosstab since you can't aggregate strings.
I created a query that does this:
id first middle last
1 bob [null] [null]
1 [null] joe [null]
1 [null] [null] smith
thinking I could use group by to coalesce them into a single record, but that didn't work.
Not sure what to do unless I try it with Domain functions,
Can you post your code? Maybe it only needs a small change.
ASKER
That could take a while. It's probably faster to just write some SQL based on the mock table above.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Before I try the transform, is there a way to do it based on the intermediate data I created (with the nulls mixed in)? That would be easier to implement.
I suggest you to drop that intermediate query since will only make things harder.
Anyway I can only give you an assertive answer if you post here the query that returns that result.
Anyway I can only give you an assertive answer if you post here the query that returns that result.
Have a look at the following EE article on DConcat(), a custom function you can use to flatten and concatenate data:
https://www.experts-exchange.com/Database/MS_Access/A_2380-Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html
Jim.
https://www.experts-exchange.com/Database/MS_Access/A_2380-Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html
Jim.
Select Distinct id, STUFF((Select ' '+name_value From tablename p1 Where p2.id=P1.id
For XML PATH('')),1,1,'') as [First Middle Last] from tablename p2
For XML PATH('')),1,1,'') as [First Middle Last] from tablename p2
ASKER
Thanks all for your comments.
Vitor - You were right. Your SQL can be adapted perfectly for this. "First" was the missing piece!
Thanks,
Mike
Vitor - You were right. Your SQL can be adapted perfectly for this. "First" was the missing piece!
Thanks,
Mike