Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2013
    Posts
    4

    Unanswered: Sql crosstab or alternate solution

    I originally posted this in the Ms Access area but had no responses. Any help would be appreciated.

    I am using Ms Access 2002. In Sql, I am trying to show employee benefit results based on employee dependents. I would like to group on employee and display one row per employee. On that row, I would like the total number of depends, and each depends age and birthdate. I tried a crosstab query;

    TRANSFORM Count(tblActiveBenefits.Birthdate) AS CountOfBirthdate
    SELECT tblActiveBenefits.employee, Count(tblActiveBenefits.DependAge) AS [Total Of DependAge]
    FROM tblActiveBenefits
    GROUP BY tblActiveBenefits.employee
    PIVOT "Age: " & tblActiveBenefits.DependAge;

    and got the following results;
    xtabActiveBenefits
    employee Total Of Depend_Age Age: 10 Age: 12 Age: 15 Age: 9
    23333 2 1 1
    1044444 1 1
    1055555 2 1 1

    What I would like to see;
    Employee Total Depends Age Birthdate Age Birthdate
    23333 2 12 12/29/2000 9 1/4/2004
    1044444 1 15 12/27/1997
    1055555 2 12 8/17/2000 10 4/4/2003

    Source Data
    CREATE TABLE tblActiveBenefits3 (employee number, DependCode number, DependAge number, Birthdate DateTime )

    INSERT INTO tblActivebenefits (employee, DependCode, DependAge, Birthdate)
    Select * From DataFile

    DataFile:
    23333, 2, 12, 12/29/2000
    23333, 2, 9, 1/4/2004
    1044444, 3, 15, 12/27/1997
    1055555, 4, 12, 8/17/2000
    1055555, 4, 10, 4/4/2003


    Is what Im thinking (array like) possible with just Sql?

    Thank you.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are ways to do what you described, but I'm not at all sure that's what you really need. How do you want to deal with a case of triplets? How do you envision using the columns that result from this query?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Apr 2013
    Posts
    4
    Hi Pat,

    Thanks for responding. If there are triplets, there would be an increment for total dependents and another occurrence of age and birthdate. There would be an occurrence for every dependent. And since this information is grouped, it would be for reporting purposes only.

    Thank you for your help,
    GeneL

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    DECLARE @t TABLE
    (  employee     INT
    ,  dependCode   INT
    ,  dependAge    INT
    ,  dependBirth  DATE
    )
    
    INSERT INTO @t
       VALUES
    (       23333, 2, 12, '12/29/2000')
    , (     23333, 2,  9, '1/4/2004')
    , (   1044444, 3, 15, '12/27/1997')
    , (   1055555, 4, 12, '8/17/2000')
    , (   1055555, 4, 10, '4/4/2003');
    
    WITH cte
    AS (
    SELECT *, Row_number() OVER (PARTITION BY employee ORDER BY dependBirth) AS n
       FROM @t AS t1
    ) SELECT d1.employee, d1.dependAge, d1.dependBirth
    ,  d2.dependAge, d2.dependBirth, d3.dependAge, d3.dependBirth
    ,  d4.dependAge, d4.dependBirth
       FROM cte AS d1
       LEFT JOIN cte AS d2 ON 2 = d2.n AND d2.employee = d1.employee
       LEFT JOIN cte AS d3 ON 3 = d3.n AND d3.employee = d1.employee
       LEFT JOIN cte AS d4 ON 4 = d4.n AND d4.employee = d1.employee
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ugh! Please forgive the "air code" above. The actual SELECT ought to be:
    Code:
    WITH cte
    AS (
    SELECT *, Row_number() OVER (PARTITION BY employee ORDER BY dependBirth) AS n
    ,  Count(*) OVER (PARTITION BY employee) AS td
       FROM @t AS t1
    ) SELECT d1.employee, d1.td AS 'Total Dependants'
    ,  d1.dependAge, d1.dependBirth, d2.dependAge, d2.dependBirth
    ,  d3.dependAge, d3.dependBirth, d4.dependAge, d4.dependBirth
       FROM cte AS d1
       LEFT JOIN cte AS d2 ON 2 = d2.n AND d2.employee = d1.employee
       LEFT JOIN cte AS d3 ON 3 = d3.n AND d3.employee = d1.employee
       LEFT JOIN cte AS d4 ON 4 = d4.n AND d4.employee = d1.employee
       WHERE  1 = d1.n
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Quote Originally Posted by Pat Phelan View Post
    Code:
     SELECT d1.employee, d1.td AS 'Total Dependants'
    ,  d1.dependAge, d1.dependBirth, d2.dependAge, d2.dependBirth
    ,  d3.dependAge, d3.dependBirth, d4.dependAge, d4.dependBirth
       FROM cte AS d1
       LEFT JOIN cte AS d2 ON 2 = d2.n AND d2.employee = d1.employee
       LEFT JOIN cte AS d3 ON 3 = d3.n AND d3.employee = d1.employee
       LEFT JOIN cte AS d4 ON 4 = d4.n AND d4.employee = d1.employee
       WHERE  1 = d1.n
    Very clever! But of course, this completely breaks down when Jim Bob Duggar is your latest hire!

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    At 4096 columns you might get tired of typing, but I guarantee you that Mr Duggar will give up long before SQL can't cope anymore!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Quote Originally Posted by Pat Phelan View Post
    At 4096 columns you might get tired of typing, but I guarantee you that Mr Duggar will give up long before SQL can't cope anymore!
    -PatP
    One can only hope so!

  9. #9
    Join Date
    Apr 2013
    Posts
    4
    Pat,

    Sorry for the delay in response. I needed to try this at home because Sql Server is not currently available to me at work. But, this is a beautiful thing and awesome solution that works flawlessly in Sql Server 2008 R2. The trick is to translate it into Ms Access if possible, because that is the only database available to me at work.

    Thank you for a very elegant solution and an introduction to Row number, Over and Partition.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ah, getting this to work in MS-Access puts a whole new spin on things... Or maybe it doesn't!

    If you are using Access 2013, then the posted code will work nicely.

    If you are using Access 2010 or earlier, then you can download SQL Express and use it.

    If that isn't an option, then I'll defer to the gurus in the MS-Access forum. My solution would be to create a pivot query to get the birth dates, bind that query to a report and have the report compute the ages (if you truly need them). This has the side benefit of always getting the ages computed based on the report date instead of relying on someone or something to keep those current for you.

    I'll move this thread to the MS-Access forum to get input from the gurus!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Tags for this Thread

Posting Permissions

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