Results 1 to 15 of 15

Thread: CrossTab Query

  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: CrossTab Query

    I am transferring a Access DB in SQL Server and trying to work a way of building a CrossTab Query.

    I have this from Access below, how can I transfer into SQL Server?

    Code:
    TRANSFORM Sum(dbo_REP_NON_PATIENT_EVENT_PERF_MAN.ACTUAL_ATTENDEES) AS SumOfACTUAL_ATTENDEES
    SELECT SueProfCarers2.SERVICE
    FROM dbo_REP_NON_PATIENT_EVENT_PERF_MAN LEFT JOIN SueProfCarers2 
    ON dbo_REP_NON_PATIENT_EVENT_PERF_MAN.PROCA_REFNO = SueProfCarers2.PROCA_REFNO
    WHERE (((dbo_REP_NON_PATIENT_EVENT_PERF_MAN.START_DTTM) Between #4/1/2008# And #9/30/2008 23:59:59#) 
    AND ((dbo_REP_NON_PATIENT_EVENT_PERF_MAN.MAIN_CODE) 
    In ("ANT","CHILD","DENT","GRPSS","HED","HPROM","PATNT","WELL")))
    GROUP BY SueProfCarers2.SERVICE
    PIVOT Mid(dbo_REP_NON_PATIENT_EVENT_PERF_MAN.START_DTTM,4,2);

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    What version of SQL Server?

    Static (fixed column list) Crosstab queries can be done in SQL Server using the CASE function.

    Dynamic CrossTabs are more difficult, though there are scripts available that will do them for you.

    SQL Server 2005 has the PIVOT function, which will crosstab results for you.

    The thing is, dynamic crosstabs/pivoting should not be done in the database in the first place. Static is OK, but with dynamic crosstabs your column headers and result set schema will vary from one execution to the next, making them nearly useless to front-ends.

    Most pivoting is display related, and should therefor be handled by your application interface or reporting tools.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    I'm using SQL Server 2005

    The thing is, dynamic crosstabs/pivoting should not be done in the database in the first place. Static is OK, but with dynamic crosstabs your column headers and result set schema will vary from one execution to the next, making them nearly useless to front-ends.
    Why would this be?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Re-read what you've quoted; the answer is there. "with dynamic crosstabs..."
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so move the data to SQL Server, link to the tables in Access, and continue doing the crosstab query in Access
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I can think of ways to handle a differing number of columns being returned in Excel VBA and classic ASP and the datagrid thingie in dotNet. A lot if it has to do with run time and not design time binding and iterating through the Fields collection (or whatever it is called) in the ADO recordset thingie or whatever they call it now.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    True... but then we come back to the original question of how we achieve this in SQL Server and that answer requires dynamic SQL afaik.
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    I have tried this to see how I get on. Taking the code from Thread1 I replicated it into SQL

    I changed the original table to incorportate what month is for the START_DTTM
    and have got this below.
    Code:
    SELECT PARA_SUEPROFCARERS2.Service,
    		SUM(CASE WHEN REP_NON_PATIENT_EVENT_PERF_MAN_DATES.THE_MONTH = 1 THEN 1 ELSE 0 END) AS January,
    		SUM(CASE WHEN REP_NON_PATIENT_EVENT_PERF_MAN_DATES.THE_MONTH = 2 THEN 1 ELSE 0 END) AS February,
    		SUM(CASE WHEN REP_NON_PATIENT_EVENT_PERF_MAN_DATES.THE_MONTH = 3 THEN 1 ELSE 0 END) AS March,
    		SUM(CASE WHEN REP_NON_PATIENT_EVENT_PERF_MAN_DATES.THE_MONTH = 4 THEN 1 ELSE 0 END) AS April,
    		SUM(CASE WHEN REP_NON_PATIENT_EVENT_PERF_MAN_DATES.THE_MONTH = 5 THEN 1 ELSE 0 END) AS May,
    		SUM(CASE WHEN REP_NON_PATIENT_EVENT_PERF_MAN_DATES.THE_MONTH = 6 THEN 1 ELSE 0 END) AS June,
    		SUM(CASE WHEN REP_NON_PATIENT_EVENT_PERF_MAN_DATES.THE_MONTH = 7 THEN 1 ELSE 0 END) AS July,
    		SUM(CASE WHEN REP_NON_PATIENT_EVENT_PERF_MAN_DATES.THE_MONTH = 8 THEN 1 ELSE 0 END) AS August,
    		SUM(CASE WHEN REP_NON_PATIENT_EVENT_PERF_MAN_DATES.THE_MONTH = 9 THEN 1 ELSE 0 END) AS September,
    		SUM(CASE WHEN REP_NON_PATIENT_EVENT_PERF_MAN_DATES.THE_MONTH = 10 THEN 1 ELSE 0 END) AS October,
    		SUM(CASE WHEN REP_NON_PATIENT_EVENT_PERF_MAN_DATES.THE_MONTH = 11 THEN 1 ELSE 0 END) AS November,
    		SUM(CASE WHEN REP_NON_PATIENT_EVENT_PERF_MAN_DATES.THE_MONTH = 12 THEN 1 ELSE 0 END) AS December,
    		SUM(REP_NON_PATIENT_EVENT_PERF_MAN_DATES.ACTUAL_ATTENDEES) AS 'Total'
    FROM REP_NON_PATIENT_EVENT_PERF_MAN_DATES LEFT JOIN PARA_SUEPROFCARERS2
    ON REP_NON_PATIENT_EVENT_PERF_MAN_DATES.PROCA_REFNO=PARA_SUEPROFCARERS2.PROCA_REFNO
    WHERE (((REP_NON_PATIENT_EVENT_PERF_MAN_DATES.START_DTTM) BETWEEN '01/04/2008 00:00:00' AND '30/09/2008 23:59:59')
    AND ((REP_NON_PATIENT_EVENT_PERF_MAN_DATES.MAIN_CODE)
    IN ('ANT', 'CHILD', 'DENT', 'GRPSS', 'HED', 'HPROM', 'PATNT', 'WELL')))
    GROUP BY PARA_SUEPROFCARERS2.Service
    It runs but the problem is that how can I include the sum of the ACTUAL_ATTENDEES as in the code from Thread1, as when I have included it as a Total it matches the total from the Access Query but its how do I show this under the relevant Month?

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    SUM(CASE WHEN REP_NON_PATIENT_EVENT_PERF_MAN_DATES.THE_MONTH = 1 THEN ACTUAL_ATTENDEES ELSE 0 END) AS January,
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Apr 2007
    Posts
    183

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Apparently he did not trust that any single forum would give the correct solution.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Apr 2007
    Posts
    183
    I know. Sometimes you need a rocket scientist.

  13. #13
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Its not a case of not trusting what you give back, I was being pushed for an answer quickly.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, tell whoever was pushing you to BACK OFF, JACK!
    Quality can't be rushed, of course.
    Plus, if you give people an answer too quickly then they think the problem was easy. Always make them wait. It builds the anticipation and suspense.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    waiting is fullness.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

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