November 26, 2012 at 9:46 am
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
November 26, 2012 at 10:07 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 26, 2012 at 3:59 pm
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!
November 26, 2012 at 4:12 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
November 26, 2012 at 9:29 pm
Yes, the columns will be identifiable by the column header which will be created/named using the "name" column of the child table.
November 27, 2012 at 7:35 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 27, 2012 at 8:16 am
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!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply