Select from two tables

Guy Hengel [angelIII / a3]

ASKER

Hi,

I know its a bad table design but I cannot change the table as its part of our ERP software.
I can create a view on that table if that helps

I have looked at the link you have provided and its above my level of SQL

UNPIVOT will still be the most efficient solution here...
it shows a concrete and simple example of how to UNPIVOT

To normalize (unpivot) your 1st table the syntax would look something like:

SELECT WorkCentre, CapacityYear, CapacityMonth, 1 as CapacityDay, CapacityAvail1 as CapacityAvail 
FROM BOMCapacity WHERE CapacityAvail1 IS NOT NULL
UNION ALL
SELECT WorkCentre, CapacityYear, CapacityMonth, 2 as CapacityDay, CapacityAvail2 as CapacityAvail 
FROM BOMCapacity WHERE CapacityAvail2 IS NOT NULL
UNION ALL
SELECT WorkCentre, CapacityYear, CapacityMonth, 3 as CapacityDay, CapacityAvail3 as CapacityAvail 
FROM BOMCapacity WHERE CapacityAvail3 IS NOT NULL
UNION ALL
...
SELECT WorkCentre, CapacityYear, CapacityMonth, 31 as CapacityDay, CapacityAvail31 as CapacityAvail 
FROM BOMCapacity WHERE CapacityAvail31 IS NOT NULL

This is not difficult, but it is tedious. This will result in a view that looks like:

Work Center CapacityYear CapacityMonth CapacityDay CapacityAvail
C102A 14 1 1 0
C102A 14 1 1 459
C102A 14 1 1 270

ASKER

Hi
I have managed to get the information in rows but I cannot work out how to get the day number in the row.

SELECT     CapacityFlag, WorkCentre, Cap, CapacityYear, CapacityMonth
FROM         (SELECT     CapacityFlag, WorkCentre, CapacityYear, CapacityMonth, CapacityAvail1, CapacityAvail2, CapacityAvail3
                       FROM          BomCapacity) P UNPIVOT (Cap FOR WorkC IN (CapacityAvail1, CapacityAvail2, CapacityAvail3)) AS unpvt
WHERE     CapacityFlag = 'W' AND CapacityYear = '14'

I get rows
CapacityFlag WorkCentre Cap CapacityYear CapacityMonth (I need a column for daynumber adding)
W C102 30 14 1
W C102 20 14 1
W C102 43 14 1
W C102 76 14 1

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Hi fyed
The UnPivot statement you posted works with the extra column but it shows CapacityAvail1, CapacityAvail2,CapacityAvail3 etc

Is there any way to change this to 1,2,3,4 etc

@Guy,

Thanks for that reference. I've been using the longer syntax for years because the last time I used SQL Server extensively was back in 2000-2002 and that feature was not available.

ASKER

@fyed,

i changed CapacityAvail1 to CapacityAvail1 AS [1] etc in the select and changed Available FOR CapacityDay IN ([1], [2], etc and it works as I require.

I will split the points between the two experts.

ASKER

Thanks to both for your input

@Rick, I failed to pickup on that in the link that Guy posted.

You might try:

SELECT WorkCentre, CapacityYear, CapacityMonth
, Replace(CapacityDay, 'CapacityAvail', '') as CapDay, Available
FROM 
   (SELECT WorkCentre, CapacityYear, CapacityMonth, CapacityAvail1,
        CapacityAvail2, CapacityAvail3, CapacityAvail4, CapacityAvail5
   FROM BOMCapacity) p
UNPIVOT
   (Available FOR CapacityDay IN 
      (CapacityAvail1, CapacityAvail2, CapacityAvail3, 
       CapacityAvail4, CapacityAvail5)
) AS unpvt;

I assume that your [Day] column in the 2nd table is numeric, so you may also need to cast or convert the value that is the result of that Replace as an integer.

SELECT WorkCentre, CapacityYear, CapacityMonth
, Cast(Replace(CapacityDay, 'CapacityAvail', '') as smallint) as CapDay
, Available
FROM 
   (SELECT WorkCentre, CapacityYear, CapacityMonth, CapacityAvail1,
        CapacityAvail2, CapacityAvail3, CapacityAvail4, CapacityAvail5
   FROM BOMCapacity) p
UNPIVOT
   (Available FOR CapacityDay IN 
      (CapacityAvail1, CapacityAvail2, CapacityAvail3, 
       CapacityAvail4, CapacityAvail5)
) AS unpvt;