Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Merging data into single record

Posted on 2014-10-08
9
Medium Priority
?
165 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 53

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 53

Accepted Solution

by:
Vitor Montalvão earned 2000 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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 

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 53

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

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

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

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

580 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