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

[thep1]

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

 

[gmmastros]

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.

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

 

[Olaf Doschke]

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

https://www.doschke.name

 

[thep1]

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.