Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to join multiple rows of subtable to parent table Expand / Collapse
Author
Message
Posted Monday, November 26, 2012 9:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, October 19, 2014 11:03 PM
Points: 4, Visits: 36
I have 2 related tables and the second table can have many rows associated with the parent table. I need to join all of rows in table 2 to the parent row in table 1 by adding columns to the row based on the child row "name". I am probably doing a terrible job at explaining this so please see example below.

Instead of creating a table (Person) with all the the metadata (PersonAttributes) in 1 table I broke it into 2 because the attributes may vary. I would like to try to accomplish the below without having to modify any special permissions....for example to use OPENROWSET.

Person Table layout:
Id - Identity
FirstName
LastName
Address
ect.

PersonAttributes Table layout:
Id - Identity
PersonId - link to "Person"
Name - HairColor, EyeColor, Weight, ect.
Value - Red, blue, 175, ect.


I would like my end result to look like this (the end result could be a little different for each person because some may have more or less attributes than others.

Dynamic table row output 1:
Fields: Id, FirstName, LastName, Address, HairColor, Weight
Values: 1, Mickey, Mouse, 123 Majic kingdom Lane, Black, 375

Dynamic table row output 2:
Fields: Id, FirstName, LastName, Address, HairColor, EyeColor
Values: 2, Minnie, Mouse, ABC Palace Lane, Pink, Green


The above is an example of my real data layout...I hope it is sufficient in explaining my problem.

Thank you in advance!
Dustin
Post #1388693
Posted Monday, November 26, 2012 10:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:38 PM
Points: 13,328, Visits: 12,825
What you are trying to do here is called a cross tab query. You will likely need to use a dynamic version of this. There is a great article referenced in my signature about rows to columns. Take a look at both of those. If you need help with the actual query then you should also take a look at the first link in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1388707
Posted Monday, November 26, 2012 3:59 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, October 19, 2014 11:03 PM
Points: 4, Visits: 36
Thanks for the quick reply Sean!
The last catch to my problem is that I then have to join this created table to other table(s) (actual tables).
Do you know how to join to the results of a stored procedure?

Thank you again in advance!
Post #1388885
Posted Monday, November 26, 2012 4:12 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 2,332, Visits: 3,512
haagendaz (11/26/2012)
I have 2 related tables and the second table can have many rows associated with the parent table. I need to join all of rows in table 2 to the parent row in table 1 by adding columns to the row based on the child row "name". I am probably doing a terrible job at explaining this so please see example below.

Instead of creating a table (Person) with all the the metadata (PersonAttributes) in 1 table I broke it into 2 because the attributes may vary. I would like to try to accomplish the below without having to modify any special permissions....for example to use OPENROWSET.

Person Table layout:
Id - Identity
FirstName
LastName
Address
ect.

PersonAttributes Table layout:
Id - Identity
PersonId - link to "Person"
Name - HairColor, EyeColor, Weight, ect.
Value - Red, blue, 175, ect.


I would like my end result to look like this (the end result could be a little different for each person because some may have more or less attributes than others.

Dynamic table row output 1:
Fields: Id, FirstName, LastName, Address, HairColor, Weight
Values: 1, Mickey, Mouse, 123 Majic kingdom Lane, Black, 375

Dynamic table row output 2:
Fields: Id, FirstName, LastName, Address, HairColor, EyeColor
Values: 2, Minnie, Mouse, ABC Palace Lane, Pink, Green


The above is an example of my real data layout...I hope it is sufficient in explaining my problem.

Thank you in advance!
Dustin




Yes, you could create such data rows (assuming you could figure out how to pad any that were short of columns), but what good would the final result be? Aside from having to CAST every value as [n]varchar, so different values could "share" a single result column, you'd have no idea what data was in a column from one row to the next.

Are you planning on including a "header" row for every result row in the output table??


SQL DBA,SQL Server MVP('07, '08, '09)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1388889
Posted Monday, November 26, 2012 9:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, October 19, 2014 11:03 PM
Points: 4, Visits: 36
Yes, the columns will be identifiable by the column header which will be created/named using the "name" column of the child table.
Post #1388927
Posted Tuesday, November 27, 2012 7:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:38 PM
Points: 13,328, Visits: 12,825
haagendaz (11/26/2012)
Thanks for the quick reply Sean!
The last catch to my problem is that I then have to join this created table to other table(s) (actual tables).
Do you know how to join to the results of a stored procedure?

Thank you again in advance!


Well technically the return from a stored procedure is an integer. You can however capture the results of a select statement from within your stored procedure. You have to first create a table that has the same structure as your select statement, then you can insert. Below is a skeleton example.

create table #MyTable
(
[COLUMNS]
)

insert #MyTable
exec MyProc



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1389196
Posted Tuesday, November 27, 2012 8:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, October 19, 2014 11:03 PM
Points: 4, Visits: 36
The hard part is creating the temp table before it is populated because the stored proc output is dynamic. The only solution I can come up with is to basically call a stored procedure that does all the dynamic stuff and returns the table structure of what the "original stored proc" will return. Use that table structure to build the temp table and then call the "original stored proc" to populate THAT temp table and then do the joining with other physical tables from there.

Aside from the performance hit I will have to take from calling the additional stored procedure, do you see any holes in this theory? Do you have any other ideas on how to populate a temp table without a predefinition of table structure? The end result is that I have to join the stored procedure results with a couple other tables, then spit the results from the final sql join into a web gridview and then the user can perform manual searching and filtering from there....this is for manual user data analysis.

I hope the above paints a decent picture and I didnt just cause confustion. Let me know if thats the case

Thank you!
Post #1389221
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse