Solved

Merging data into single record

Posted on 2014-10-08
9
163 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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 58
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
 

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

688 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