November 14, 2012 at 2:40 pm
Ok. The end result is of this query is to be used in SQL server. I need to group records for "Bin" and present them per line as a single field. So I will be first making a view to limit the results to the conditions needed. The view will simply contain an Item number and the Bin. I will have multiple bins per item.
What I need to do, is to now take this and provide a view that will display "ITEM", "BIN", "BIN", "BIN"... where Bin's are either a single record or individual columns that I can then combine via Crystal Reports. The catch is that the number of bins per item is either NULL or unknown. Could be 3 could be 15.
November 14, 2012 at 2:49 pm
jmceuin (11/14/2012)
Ok. The end result is of this query is to be used in SQL server. I need to group records for "Bin" and present them per line as a single field. So I will be first making a view to limit the results to the conditions needed. The view will simply contain an Item number and the Bin. I will have multiple bins per item.What I need to do, is to now take this and provide a view that will display "ITEM", "BIN", "BIN", "BIN"... where Bin's are either a single record or individual columns that I can then combine via Crystal Reports. The catch is that the number of bins per item is either NULL or unknown. Could be 3 could be 15.
From the description I don't think you will be able to do this in a view. If at all possible you should pivot this data in Crystal instead of in SQL. If you really want to try this in sql you should read the two articles in my signature about cross tabs with heavy focus on the second one. The first article lays the foundation and the second goes into details about how to do this with a dynamic number of columns.
_______________________________________________________________
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/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply