Solved

Merging data into single record

Posted on 2014-10-08
9
161 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
9 Comments
 
LVL 49

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 49

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 49

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

762 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