Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2010
    Posts
    1

    Talking Unanswered: Help, SQL Multiple Pivot

    Hi to all, i was trying to make a pivot for this format

    ID Jan/Male Jan/Female Feb/Male Feb/Female March/Male March/Female
    1 1 1 2 3 3 1
    2 2 2 4 2 1 2
    3 3 3 2 2 2 3
    4 4 4 3 1 3 2

    the Values in each columns are the total count for each month
    Here is my Pivot Query but not the result i want.

    SELECT PVT1.REGION,
    PVT1.BRANCH,
    PVT1.[MALE] AS Male,PVT1.[FEMALE] AS Female,
    PVT2.[MALE] AS Male,PVT2.[FEMALE] AS Female,
    PVT3.[MALE] AS Male,PVT3.[FEMALE] AS Female
    FROM(
    SELECT CUSTOMERINFORMATION.REGION,
    CUSTOMERINFORMATION.BRANCH,
    CUSTOMERINFORMATION.GENDER,
    CUSTOMERINFORMATION.CIFNUMBER,
    DATENAME(MONTH,DEPOSITINFORMATION.APPLICATIONDATE) + ' ' + DATENAME(YEAR,DEPOSITINFORMATION.APPLICATIONDATE) AS APPLICATIONDATE
    FROM CUSTOMERINFORMATION
    INNER JOIN DEPOSITINFORMATION
    ON CUSTOMERINFORMATION.CIFNUMBER=DEPOSITINFORMATION.C IFNUMBER
    WHERE DATENAME(MONTH,DEPOSITINFORMATION.APPLICATIONDATE) ='JANUARY' AND DATENAME(YEAR,APPLICATIONDATE)='1996') AS TABLE1
    PIVOT(COUNT(CIFNUMBER) FOR GENDER IN ([MALE],[FEMALE]))AS PVT1
    JOIN(
    SELECT CUSTOMERINFORMATION.BRANCH,
    CUSTOMERINFORMATION.CIVILSTATUS,
    CUSTOMERINFORMATION.GENDER,
    CUSTOMERINFORMATION.CIFNUMBER,
    DATENAME(MONTH,DEPOSITINFORMATION.APPLICATIONDATE) + ' ' + DATENAME(YEAR,DEPOSITINFORMATION.APPLICATIONDATE) AS APPLICATIONDATE
    FROM CUSTOMERINFORMATION
    INNER JOIN DEPOSITINFORMATION
    ON CUSTOMERINFORMATION.CIFNUMBER=DEPOSITINFORMATION.C IFNUMBER
    WHERE DATENAME(MONTH,DEPOSITINFORMATION.APPLICATIONDATE) ='FEBRUARY' AND DATENAME(YEAR,APPLICATIONDATE)='1996') AS TABLE2
    PIVOT(COUNT(CIFNUMBER) FOR CIVILSTATUS IN ([MALE],[FEMALE]))AS PVT2
    ON PVT1.BRANCH = PVT2.BRANCH
    JOIN(
    SELECT CUSTOMERINFORMATION.BRANCH,
    CUSTOMERINFORMATION.JOBTYPE,
    CUSTOMERINFORMATION.GENDER,
    CUSTOMERINFORMATION.CIFNUMBER,
    DATENAME(MONTH,DEPOSITINFORMATION.APPLICATIONDATE) + ' ' + DATENAME(YEAR,DEPOSITINFORMATION.APPLICATIONDATE) AS APPLICATIONDATE
    FROM CUSTOMERINFORMATION
    INNER JOIN DEPOSITINFORMATION
    ON CUSTOMERINFORMATION.CIFNUMBER=DEPOSITINFORMATION.C IFNUMBER
    WHERE DATENAME(MONTH,DEPOSITINFORMATION.APPLICATIONDATE) ='MARCH' AND DATENAME(YEAR,APPLICATIONDATE)='1996') AS TABLE3
    PIVOT(COUNT(CIFNUMBER) FOR JOBTYPE IN ([MALE],[FEMALE]))AS PVT3
    ON PVT2.BRANCH = PVT3.BRANCH
    ORDER BY PVT1.BRANCH

  2. #2
    Join Date
    Jun 2008
    Posts
    19
    Can you show a better example of what you want it to look like, and what the result set does look like?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •