Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

2 table rows data in single row (master row & child rows)

2 table rows data in single row (master row & child rows)

2 table rows data in single row (master row & child rows)

I am not sure how to do this but I have 2 tables Items and Itemdetails and I am looking to get the info in one row from both tables

Items table
ItemID Name
------ ----------
80015 CLS SB
90001 Int Line
90586 Deep B44

Itemdetails table
RefID Location QTY
------ --------- ---
80015 CA 50
80015 NY 100
80015 IL 5000
90001 CA 0
90001 NY 10000
90586 FL 25

The way i want to get the data is in one row like this:

80015 CLS SB CA 50 NY 100 IL 5000 FL 0
90001 Int Line CA 0 NY 10000 IL 0 FL 0
90586 Deep B44 CA 0 NY 0 IL 0 FL 25

hope someone can help me with example or something, i been googling for this, maybe i dont know the terms to search

RE: 2 table rows data in single row (master row & child rows)


i been googling for this, maybe i dont know the terms to search

The result you want can be obtained by using the PIVOT keyword. I encourage you to google with "Sql Server Pivot". I guarantee you will find plenty of examples for writing this type of query. If you continue to have problems writing this query, let us know, and we will help more.

Microsoft SQL Server MVP
My Blogs
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: 2 table rows data in single row (master row & child rows)

There is another answer to this, you might want to finally have this output on a form or report, but it's likely easier to do once you have the simple joined data with its redundancies and multiple rows per RefID as you get it from simply joining by RefID:


SELECT * From Items I Left Join ItemDetails ID On ID.RefID = I.ItemID 

For example, you can easily fill in a sheet or grid iterating these result records and staying in the same line as long as the ItemId stays the same just incrementing the column for the next few Location/Qty data pairs. And when the ItemID changes incrementing the row and resetting Column to 1.

This is the usual way to work with data collections, no matter if they come from SQL or would have been an array or collection of same structured data coming from elsewhere, JSON or XML.

Indeed at times it can be vastly simpler to know the list of simple tuples, I just need to think about a treeview control, which organises it's layout automatically by you providing data in related form, by adding child nodes to the same parent, in a way that is exactly that, just all the child nodes of the same ItemID go into the same row and not a nested branch.

It's not the job of the database to format your output, it's the job to store your data normalized so you can have any query/composition of the data joined to then be post-processed by the frontend. There are good ways to make use of a well-proportioned server to aggregate data before the aggregation is sent back instead of sending the raw data. You want to achieve a balance of server and network resources, but don't be lazy on the client side. Such formattings are no good way to edit and update data, they are solely good for a more condensed overview, but I imagine managers capable of writing some excel macro to aggregate these, needing code for all the columns, of which there are individually many per ItemID, even within Excel you have it easier to aggregate data in many ways, if the data is a simpler list.

Bye, Olaf.

Olaf Doschke Software Engineering

RE: 2 table rows data in single row (master row & child rows)

thanks gmmastros, I searched for it and came up with article that I was exactly looking for. Converting Rows to Columns (PIVOT) and Columns to Rows (UNPIVOT) in SQL Server I used the dynamic Pivot method and was able to get the data display and create the stored procedure, but now I need to display data in Visual studio gridview but when I call the stored procedure I only get the headers row only. I think that will be new thread I guess.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close