Solved

Merging data into single record

Posted on 2014-10-08
9
162 Views
Last Modified: 2014-10-08
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,
0
Comment
Question by:shacho
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40367913
Can you post your code? Maybe it only needs a small change.
0
 

Author Comment

by:shacho
ID: 40367975
That could take a while.  It's probably faster to just write some SQL based on the mock table above.
0
 
LVL 50

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40367976
Please check if the following command works:
TRANSFORM First(name_value) AS name_value
 SELECT ID
 FROM YourTableName
 GROUP BY ID
 PIVOT name_type

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:shacho
ID: 40368001
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.
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40368011
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.
0
 
LVL 57
ID: 40368034
Have a look at the following EE article on DConcat(), a custom function you can use to flatten and concatenate  data:

http://www.experts-exchange.com/Database/MS_Access/A_2380-Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html

Jim.
0
 
LVL 2

Expert Comment

by:Pratik Makwana
ID: 40368064
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
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40368113
0
 

Author Comment

by:shacho
ID: 40368118
Thanks all for your comments.

Vitor - You were right.  Your SQL can be adapted perfectly for this.  "First" was the missing piece!

Thanks,

Mike
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question