I had to deliver an excel file to an engineer that identified the basic measurements of our base products. Our specifications system maintains every official revision of each of our products in a normalized table with all history in the same table, no header record for all of this detail. This data is in SQL Server but you can pivot in all of the backend systems today I believe.
This script pulls a max revision # and MPRD, base finished good, to be used inside of the pivot query. The table has a column for the Attribute for the spec which holds everything we need for production and testing in real time. List below includes the upper limit, lower limit as well as target for only TWO attributes of which we may have 50-99 depending on the bottle. 300 Weight Target 301 Weight LSL 302 Weight USL 303 Overall Height Target 304 Overall Height LSL 305 Overall Height USL I was given a list of Attributes for this report and you will see them used in the output headers as well as the hook for the engine to know what data to place where. -- in TSQL is a comment and can be used inline. All of this data is String thus I used Coalesce below to pull it properly. -- Get the base data for the join in the query. select max(REVISION) Rev, [OBJECT_ID] MPRD Into #MaxRev from RING_DOC_CLASSIFICATION_HISTORY where OBJECT_ID in ( select distinct ATTR_STRING_VALUE MPRD from RING_DOC_CLASSIFICATION_HISTORY where ATTR_NAME = '100 MPRD' -- get all MPRDs ) group by OBJECT_ID -- Define the columns for output Spaces in the labels need [ ] to be presented. select MPRD , Coalesce([110 Description],'') [110 Description] , Coalesce([107 Material Type],'') [107 Material Type] , Coalesce( [111 Neck Type],'') [111 Neck Type] , Coalesce( [300 Weight Target],'') [300 Weight Target] , Coalesce([303 Overall Height Target],'') [303 Overall Height Target] , Coalesce([306 Overall Width Target],'') [306 Overall Width Target] , Coalesce([309 Overall Depth Target],'') [309 Overall Depth Target] , Coalesce([318 Overflow Capacity Target],'') [318 Overflow Capacity Target] , Coalesce([109 Category],'') [109 Category] , Coalesce([321 Major Diameter Target],'') [321 Major Diameter Target] , Coalesce([324 Minor Diameter Target],'') [324 Minor Diameter Target] from -- Query to pull the data ( select MPRD, rd.ATTR_NAME, ATTR_STRING_VALUE from #MaxRev mr inner join RING_DOC_CLASSIFICATION_HISTORY rd on mr.MPRD = rd.OBJECT_ID and mr.Rev = rd.REVISION where ATTR_NAME in( '110 Description', '107 Material Type', '111 Neck Type', '300 Weight Target' , '303 Overall Height Target', '306 Overall Width Target', '309 Overall Depth Target' , '318 Overflow Capacity Target', '109 Category', '321 Major Diameter Target' , '324 Minor Diameter Target' ) --order by MPRD, Rev, ATTR_NAME ) d -- manipulate the data into output shell provided above pivot ( MAX(ATTR_STRING_VALUE) for ATTR_NAME in ( -- this is the mapping back to the table defined above [110 Description], [107 Material Type], [111 Neck Type], [300 Weight Target] , [303 Overall Height Target], [306 Overall Width Target], [309 Overall Depth Target] , [318 Overflow Capacity Target], [109 Category], [321 Major Diameter Target] , [324 Minor Diameter Target]) ) piv drop table #MaxRev -- Stephen Russell Sr. Analyst Ring Container Technology Oakland TN 901.246-0159 cell --- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html --- _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/CAJidMY+U_qMkVh4RmdHxBXnThX5NTD-W848ei=+KGKL8_8HN=a...@mail.gmail.com ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
