2

So I have the below query written but I'm running into a problem with the result set. The only way I currently know how to solve this problem would involve creating MULTIPLE views, and doing several left joins, however I suspect this is the least efficient way to do it. I need to grab some of the rows and convert them to columns, I'm just not sure how to do it.

use TSTCO
GO

select job.MANUFACTUREORDER_I as MONumber, job.ENDDATE as DueDate, job.ITEMNMBR, item.ITEMDESC, MANUFACTUREORDPRI_I as MOPriority, bom.CPN_I,
item2.ITEMDESC
from WO010032 job
left join iv00101 item on job.ITEMNMBR=item.ITEMNMBR
left join BM010115 bom on bom.PPN_I = job.ITEMNMBR and bom.BOMNAME_I = job.BOMNAME_I
left join iv00101 item2 on bom.CPN_I = item2.ITEMNMBR
where MANUFACTUREORDER_I like '2007-13580'
and (item2.itemdesc like '10:%' or item2.itemdesc like '09:%'  or item2.itemdesc like '101:%'  or item2.itemdesc like '12:%'  or item2.itemdesc like '13:%'  or item2.itemdesc like '131:%'  or item2.itemdesc like '14:%'  or item2.itemdesc like '15:%'  or item2.itemdesc like '16:%'  or item2.itemdesc like '17:%'  or item2.itemdesc like '18:%'  or item2.itemdesc like '21:%'  or item2.itemdesc like '22:%'  or item2.itemdesc like '23:%'  or item2.itemdesc like '24:%')
Order by item2.Itemdesc

My current result set looks like this:

MONumber                        DueDate                 ITEMNMBR                        ITEMDESC                                                                                              MOPriority CPN_I                           ITEMDESC
------------------------------- ----------------------- ------------------------------- ----------------------------------------------------------------------------------------------------- ---------- ------------------------------- -----------------------------------------------------------------------------------------------------
2007-13580                      2013-11-08 00:00:00.000 111.3101                        High Cut Full Back, Gripper Leg                                                                       2          JP1903-000                      10: PFP Lycra/H 5219                                                                                 
2007-13580                      2013-11-08 00:00:00.000 111.3101                        High Cut Full Back, Gripper Leg                                                                       2          JP2203-000                      10: Std 88/12 Lycra:White                                                                            
2007-13580                      2013-11-08 00:00:00.000 111.3101                        High Cut Full Back, Gripper Leg                                                                       2          Z-GRAPHIC DESIGN 15 SHOTGUN     12: Z-Graphic Design 15 Shotgun                                                                      
2007-13580                      2013-11-08 00:00:00.000 111.3101                        High Cut Full Back, Gripper Leg                                                                       2          Z-PANEL A BLACK                 13: Panel A Black                                                                                    
2007-13580                      2013-11-08 00:00:00.000 111.3101                        High Cut Full Back, Gripper Leg                                                                       2          Z-PANEL B BROWN                 13: Panel B Brown                                                                                    
2007-13580                      2013-11-08 00:00:00.000 111.3101                        High Cut Full Back, Gripper Leg                                                                       2          Z-PANEL C CARDINAL              13: Panel C Cardinal                                                                                 
2007-13580                      2013-11-08 00:00:00.000 111.3101                        High Cut Full Back, Gripper Leg                                                                       2          Z-NECK TRIM BLACK               14: NeckTrim Black                                                                                   
2007-13580                      2013-11-08 00:00:00.000 111.3101                        High Cut Full Back, Gripper Leg                                                                       2          Z-THREAD FLATLOCK:BROWN         16: Flatlock Thread Color:Brown

What I would like it to look like:

MONumber                        DueDate                 ITEMNMBR                        ITEMDESC                          MOPriority FABRIC                             Graphic                           Panel A               Panel B              Panel C                Neck Trim            Flatlock Thread
------------------------------- ----------------------- ------------------------------- --------------------------------- ---------- ---------------------------------  --------------------------------- --------------------  -------------------- --------------------   -------------------  --------------------------------
2007-13580                      2013-11-08 00:00:00.000 111.3101                        High Cut Full Back, Gripper Leg   2          10: PFP Lycra/H 5219               12: Z-Graphic Design 15 Shotgun   13: Panel A Black     13: Panel B Brown    13: Panel C Cardinal   14: NeckTrim Black   16: Flatlock Thread Color:Brown

I don't expect anyone to do anything for me, but some guidance would be greatly appreciate. I think Pivot may be where I need to start, but I'm not too sure. When I took a look at the PIVOT syntax I wasn't really sure where to begin. As you can see from the first block of code, the columns will always start with certain numbers, like fabric always begins with "10:". The text after the number is the part that changes, that I will need to return. It's also worth nothing that some columns will end up being "NULL" as not everything will have Neck Trim, etc.

Thanks in advance for any help.

LearningCurve
  • 101
  • 1
  • 1
  • 7
  • Pivot might work, can we always count on those same columns? – Daniel Gimenez Nov 10 '13 at 21:10
  • this is a similar question with answer: – Inbal Abraham Nov 10 '13 at 21:20
  • Not always the same columns. There are actually a list of things I'm looking for if you look at the second line of the where clause. I'm totally fine with having all the possible columns returned and then having Null values. I only have a particular MO number in there for testing, but I plan to create a view once I nail down the statement. – LearningCurve Nov 10 '13 at 21:25
  • Thinking about it, Pivot isn't right since that's for aggregate data. If the columns are going to change then you'll need to create a query that can handle all the possible columns. – Daniel Gimenez Nov 10 '13 at 21:31
  • Hey out of curiosity is this Greatplains? – Daniel Gimenez Nov 10 '13 at 21:43
  • Yes, GP 10.0. Look familiar? Working with this system is my first experience with SQL so what I know is spotty as I've only taught myself things as I've needed it. – LearningCurve Nov 10 '13 at 23:44
  • I did GP 13 years ago, glad to see table definitions haven't changes. – Daniel Gimenez Nov 11 '13 at 01:20

1 Answers1

0

How about preparing your data using one or two CTE frist and then dynamic pivot query.

Please see a link below, which might be useful for your scenario or at least can give you a clue.

Script to create dynamic pivot query

Ronnie Rahman
  • 1
  • 1