UNPIVOT two fields

8 Dec 2005 7:51 PM

cliffeh

I have a table that looks like this:

Customer | PartNumber | Jan | Jan$ | Feb | Feb$ | Mar | Mar$ ...

The data in Jan, Feb, Mar... are the sales quantities for the month, and the
data in Jan$, Feb$ and Mar$ are the sales dollars for the month.

I would like to unpivot it, so that the Jan,Feb,Mar.. columns become
"Quantity" and the Jan$, Feb$, Mar$... columns become "Dollars."

I'm able to do this with two separate procedures:

SELECT Customer,PartNumber,[Month],Quantity FROM
    (SELECT Customer,PartNumber,[Jan],[Feb],[Mar] FROM [Plan]) PlanTotal
    UNPIVOT (Quantity FOR [Month] IN
        ([Jan],[Feb],[Mar])) AS unpivoted

does the quantity, and

SELECT Customer,Mapics,PartNumber,[Month],Dollars FROM
    (SELECT Customer,Mapics,PartNumber,[Jan$],[Feb$],[Mar$] FROM [Plan])
PlanTotal
    UNPIVOT (Dollars FOR [Month] IN
        ([Jan$],[Feb$],[Mar$])) AS unpivoted

does the Dollars.

Can I join those two queries?

Thanks,
Cliffe

8 Dec 2005 8:42 PM

Kent Tegels

Hello cliffeh,

Union All between the two outer selects will get you part of the way there,
I think.

Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

8 Dec 2005 9:16 PM

cliffeh

Thanks for the reply, Kent.

However, UNION ALL gives me a table with columns Customer, PartNumber, Month
& Quantity, but then the dollar values get appended to the bottom, and the
dollar values go in the quantity field.

I need to do a JOIN, where I join the 2 select statements on Customer,
PartNumber & Month. Something like this:

(SELECT Customer,PartNumber,[Month],Quantity FROM
    (SELECT Customer,Mapics,PartNumber,[Jan],[Feb],[Mar] FROM [Plan]) PlanTotal
    UNPIVOT (Quantity FOR [Month] IN
        ([Jan],[Feb],[Mar])) AS unpivoted1
)
JOIN
(
SELECT Customer,PartNumber,[Month],Dollars FROM
    (SELECT Customer,Mapics,PartNumber,[Jan$],[Feb$],[Mar$] FROM [Plan])
PlanTotal
    UNPIVOT (Dollars FOR [Month] IN
        ([Jan$],[Feb$],[Mar$])) AS unpivoted2
)
ON unpivoted1.customer=unpivoted2.customer AND
unpivoted1.PartNumber=unpivoted2.partNumber
AND unpivoted1.month=unpivoted2.month

But that gives me the error "Incorrect syntax near the keyword 'JOIN'" and
"Incorrect syntax near the keyword 'ON'"

Thanks,
Cliffe

Show quote

"Kent Tegels" wrote:

> Hello cliffeh,
>
> Union All between the two outer selects will get you part of the way there,
> I think.
>
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>
>

8 Dec 2005 9:46 PM

Erland Sommarskog

cliffeh (clif***@discussions.microsoft.com) writes:
Show quote

> I need to do a JOIN, where I join the 2 select statements on Customer,
> PartNumber & Month. Something like this:
>
> (SELECT Customer,PartNumber,[Month],Quantity FROM
>      (SELECT Customer,Mapics,PartNumber,[Jan],[Feb],[Mar] FROM [Plan])
>      PlanTotal
>      UNPIVOT (Quantity FOR [Month] IN
>           ([Jan],[Feb],[Mar])) AS unpivoted1
> )
> JOIN
> (
> SELECT Customer,PartNumber,[Month],Dollars FROM
>      (SELECT Customer,Mapics,PartNumber,[Jan$],[Feb$],[Mar$] FROM [Plan])
> PlanTotal
>      UNPIVOT (Dollars FOR [Month] IN
>           ([Jan$],[Feb$],[Mar$])) AS unpivoted2
> )
> ON unpivoted1.customer=unpivoted2.customer AND
> unpivoted1.PartNumber=unpivoted2.partNumber
> AND unpivoted1.month=unpivoted2.month
>
> But that gives me the error "Incorrect syntax near the keyword 'JOIN'" and
> "Incorrect syntax near the keyword 'ON'"

I'm not really up to pace on UNPIVOT yet, but this query at least passes
the syntax check:

   SELECT *
   FROM   (SELECT Customer,PartNumber,[Month],Quantity
           FROM (SELECT Customer,Mapics,PartNumber,[Jan],[Feb],[Mar]
                  FROM   [Plan]) PlanTotal
                  UNPIVOT (Quantity FOR [Month] IN ([Jan],[Feb],[Mar]))
                  AS x
           ) unpivoted1
   JOIN   (SELECT Customer,PartNumber,[Month],Dollars
           FROM   (SELECT Customer,Mapics,PartNumber,[Jan$],[Feb$],[Mar$]
                   FROM [Plan]) PlanTotal
                   UNPIVOT (Dollars FOR [Month] IN ([Jan$],[Feb$],[Mar$]))
                   AS y
            ) unpivoted2
           ON unpivoted1.Customer=unpivoted2.Customer AND
              unpivoted1.PartNumber=unpivoted2.PartNumber AND
              unpivoted1.Month=unpivoted2.Month

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

9 Dec 2005 1:45 PM

cliffeh

Perfect! Thank you!

The only tweak I had to make was that the Month column for the dollar values
had a trailing "$", so I chose the left n-1 characters.

Thanks again,
Cliffe

Show quote

"Erland Sommarskog" wrote:

> cliffeh (clif***@discussions.microsoft.com) writes:
> > I need to do a JOIN, where I join the 2 select statements on Customer,
> > PartNumber & Month. Something like this:
> >
> > (SELECT Customer,PartNumber,[Month],Quantity FROM
> >      (SELECT Customer,Mapics,PartNumber,[Jan],[Feb],[Mar] FROM [Plan])
> >      PlanTotal
> >      UNPIVOT (Quantity FOR [Month] IN
> >           ([Jan],[Feb],[Mar])) AS unpivoted1
> > )
> > JOIN
> > (
> > SELECT Customer,PartNumber,[Month],Dollars FROM
> >      (SELECT Customer,Mapics,PartNumber,[Jan$],[Feb$],[Mar$] FROM [Plan])
> > PlanTotal
> >      UNPIVOT (Dollars FOR [Month] IN
> >           ([Jan$],[Feb$],[Mar$])) AS unpivoted2
> > )
> > ON unpivoted1.customer=unpivoted2.customer AND
> > unpivoted1.PartNumber=unpivoted2.partNumber
> > AND unpivoted1.month=unpivoted2.month
> >
> > But that gives me the error "Incorrect syntax near the keyword 'JOIN'" and
> > "Incorrect syntax near the keyword 'ON'"
>
> I'm not really up to pace on UNPIVOT yet, but this query at least passes
> the syntax check:
>
>    SELECT *
>    FROM   (SELECT Customer,PartNumber,[Month],Quantity
>            FROM (SELECT Customer,Mapics,PartNumber,[Jan],[Feb],[Mar]
>                   FROM   [Plan]) PlanTotal
>                   UNPIVOT (Quantity FOR [Month] IN ([Jan],[Feb],[Mar]))
>                   AS x
>            ) unpivoted1
>    JOIN   (SELECT Customer,PartNumber,[Month],Dollars
>            FROM   (SELECT Customer,Mapics,PartNumber,[Jan$],[Feb$],[Mar$]
>                    FROM [Plan]) PlanTotal
>                    UNPIVOT (Dollars FOR [Month] IN ([Jan$],[Feb$],[Mar$]))
>                    AS y
>             ) unpivoted2
>            ON unpivoted1.Customer=unpivoted2.Customer AND
>               unpivoted1.PartNumber=unpivoted2.PartNumber AND
>               unpivoted1.Month=unpivoted2.Month
>
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>