How do I join a parent and child recordsets into one recordset?

Question

If this is a DBA question, feel free to let me know.

We have data that is stored in an SQL database that needs to be exported, so it can be converted into a new software package. Currently, we have a parent recordset and a child recordset that needs to be combined because the new system has the ability to track what was in a child recordset on the parent. I hope this makes sense.

Parent Recordset

ID | Description 
-- | ----------- 
1  | Item 1
2  | Item 2
3  | Item 3

Child recordset

Parent ID | Code        | Value (string)
--------- | ----------- | -------------------
1         | PSI         | 75
1         | SIZE        | 2 1/2"
2         | CFM         | 9200
2         | BELT        | BROWING - A76
2         | RPM         | 722
3         | PSI         | 45
3         | SIZE        | 1"

Ideally, we need a CSV file (in the end) that somehow looks like:

ID | Description | PSI | SIZE | CFM | BELT | RPM
-- | -----------
1  | Item 1
2  | Item 2
3  | Item 3

You get the picture.

I don't care if it's done in SQL, Excel, Access, or some magic (trying to avoid writing a program), I am trying to save the time it would take a staff member to manually change the order or type it in. Any ideas on how to make this easy to change around? We are talking about 5700 child records and 5900 parent records. Is there some SQL magic that can do this?

I did think about adding each column individually (e.g. (select value from child where id = parent id and code = 'RPM') as RPM) but with 157 different codes, that isn't ideal either.

What you're describing appears to be a PIVOT operation. If you search with that keyword, you should find workable SQL solutions (especially if you're using sql-server as tags indicate) — Mark Adelsberger, Mar 02 '17 at 22:19
take a look at http://stackoverflow.com/questions/24470/sql-server-examples-of-pivoting-string-data — Ian Kenney, Mar 02 '17 at 22:19
I should point out, btw, that any SQL solution will require you to specifically type out the codes you want used as columns (which, from the sound of it, may not be ideal due to number of fields). An OLAP or reporting tool might be able to do something better if you have one at your disposal. — Mark Adelsberger, Mar 02 '17 at 22:21
@mark-adelsberger - If the objective is to generate a csv file in a particular format, then the columns will need specifying at some point in the process — Ian Kenney, Mar 02 '17 at 22:23
@MarkAdelsberger I think doing it once is still better than multiple times. I'll take a look. Can this same thing be done in Excel? — Mike Wills, Mar 02 '17 at 22:33

John Cappelletti · Answer 1 · 2017-03-02T22:57:30.987

Assuming you want to go DYNAMIC (not tested)

Declare @SQL varchar(max) = Stuff((Select Distinct ',' + QuoteName(Code) From Child  Order by 1 For XML Path('')),1,1,'') 
Select  @SQL = '
Select [ID],[Description],' + @SQL + '
From (
        Select A.ID
              ,A.Description
              ,B.Code
              ,B.Value
         From Parent A
         Join Child  B on (A.ID=B.ParentID)
     ) A
 Pivot (max(Value) For [Code] in (' + @SQL + ') ) p'
Exec(@SQL);

EDIT

If the columns need to be in a specific order

Select A.ID
      ,A.Description 
      ,PSI  = max(case when B.Code='PSI'  then B.Value end)
      ,Size = max(case when B.Code='Size' then B.Value end)
      ,CFM  = max(case when B.Code='CFM'  then B.Value end)
      -- ... more fields
From  Parent A
Join  Child  B on (A.ID=B.ParentID)
Group By A.ID,A.Description

Kris Tyte · Answer 2 · 2017-03-04T06:54:54.817

I think you can pull it off in SQL using the following...

SELECT
    parent_id AS `ID`,
    MAX(CASE WHEN `code` = 'PSI'  THEN `value` ELSE NULL END) AS `PSI`,
    MAX(CASE WHEN `code` = 'SIZE' THEN `value` ELSE NULL END) AS `SIZE`,
    MAX(CASE WHEN `code` = 'CFM'  THEN `value` ELSE NULL END) AS `CFM`,
    MAX(CASE WHEN `code` = 'BELT' THEN `value` ELSE NULL END) AS `BELT`,
    MAX(CASE WHEN `code` = 'RPM'  THEN `value` ELSE NULL END) AS `RPM`
FROM
    `child`
GROUP BY
    `parent_id`

Try making a CASE for each of the fields you need following the pattern.
The MAX is an aggregate function it is used here to avoid additional grouping levels.

You can deal with a large list of Codes by first grabbing the list of Codes using...

SELECT DISTINCT `code` from `child`

Then copying the results to Excel and using the CONCATENATE function to build the MAX(CASE... lines... an example here... Excel SQL Building Example

You can also build the lines out in bulk manually with a text editor capable of handling block edits (for example Notepad++) by typing the repeated portions of the lines simultaneously in block editing mode (ALT + Click & Drag). You may also use a couple clever find and replace operations to build the lines for you as well.

Lastly, you can build the SQL by writing a small loop using your favorite language and using the Distinct code list, I built an example using JavaScript, here is a fiddle for it, it should be usable directly from the fiddle.

I've updated the post to include several ways to handle the repetition accurately and with minimal manual work building off the original concept. — Kris Tyte, Mar 04 '17 at 06:58

score 0 · Answer 3 · answered Mar 03 '17 at 13:45

I like the current data structure much better than the one you are converting to, but if you have to do it you can use VBA to loop through all the fields and create a SQL string:

Function ConvertTable()
Set db = CurrentDb()
SQL = "Select ParentID"
Set RS = db.OpenRecordset("select Code from ChildRS group by Code")
RS.MoveFirst
Do While Not RS.EOF
    code = RS.Fields("Code").Value
    SQL = SQL & ", FIRST(iif(code='" & code & "',value)) as [" & code & "]"
    RS.MoveNext
    Loop
SQL = SQL & " Into [ConvertedTable] From ChildRS group by ParentID"
db.Execute SQL
MsgBox ("done")

End Function

This works for the few codes you specified - I don't know if it will exceed limits on maximum SQL string length when you run it on all the fields. If it does, you may need to figure out how to segment the data and do it in a few pieces.

score 0 · Accepted Answer · answered Mar 03 '17 at 15:29

This is what I finally did, kind of a merge of @JohnCappelletti and others on this list.

First I created a list of all possible code

select (REPLACE(REPLACE(REPLACE(c.Code, ' ', '_'), '.',''),'/','_') + ' =
max(case when d.Code = ''' + c.Code + '''  then d.Value else '''' end),') as text
from code_definitiions c
order by Code

The output of that query is

AGMA = max(case when d.Code = 'AGMA'  then d.Value1 else '' end),
AMB = max(case when d.Code = 'AMB'  then d.Value1 else '' end),
AMB_T = max(case when d.Code = 'AMB T'  then d.Value1 else '' end),
...

Then I just copied and pasted the results in the the parent query.

select m.ID, m.Description,

AGMA = max(case when d.Code = 'AGMA'  then d.Value else '' end),
AMB = max(case when d.Code = 'AMB'  then d.Value else '' end),
AMB_T = max(case when d.Code = 'AMB T'  then d.Value else '' end),
... (the rest of the codes)

from parent m left outer join child d 
   on m.primary_key = d.foreign_key
group by m.ID, m.Description
order by m.ID

That at least achieved what I intended. The users still have cleanup to do, but it's better than manually typing.

How do I join a parent and child recordsets into one recordset?

4 Answers4