Parent / Child tables - SELECT QUERY to bring child data records into parent record

Question

Parent / Child tables - SELECT QUERY to bring child data records into parent record

rmcfaden

SSC Eights!

Points: 889
More actions
March 23, 2018 at 12:48 am

#336750

I have two tables, one with Summary/basic data, the other with detail data multiple records pertaining to each record in the Parent table.
The number of records in the detail table for each parent record varies by Site. I need to select all of the data in the parent records and bring each child record together in the one row for the parent record.
I'm guessing what I am trying to figure out how to do is somewhat basic, but I wasn't sure how to go about searching, though I tried,
Data is similar to

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

rmcfaden SSC Eights! Points: 889 More actions · Answer 1

Oh, by the way, I have another table that contains the number of Indexes in each site, or rather lists the names for each index along with the index number and some other info about each index.
This table has the per Site index as well as an overall (table) index for each department (is what they are actually).

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

It's called a Crosstab or Pivot query. An excellent article by ssc's Jeff Moden shows how to do it:

http://www.sqlservercentral.com/articles/T-SQL/63681/

Holler if you need a hand. If you do need a hand, please post up readily-consumable data.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Eirikur Eiriksson SSC Guru Points: 182879 More actions · Answer 3

Further on Chris's request for consumable data, could you post the DDL (create table) scripts, sample data as an insert statement, the desired results and what you have tried so far please?
😎

rmcfaden SSC Eights! Points: 889 More actions · Answer 4

I have been learning a lot about FOR XML, Pivot, and Unpivot in the past few days. I believe I have what I need using those tools.
I was able to create a few dynamic pivots with multiple aggregates. It has been quite a learning experience and fun.
I am grateful for all you wizzes who share your time and talent with those of us who merely dabble or are just getting into SQL coding, etc.