Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivoting Data - Best method? (2000/SQL Server 2000)

    Good morning to all...

    I have already found a solution to my need, but I'm curious to know if anyone has any better ideas or suggestions. Performance is not a critical issue, but efficiency and good design are goals that I always strive to meet.

    I have developed a Data-driven Web-based Survey (in 7 languages) stored in SQL Server (2000). It's totally dynamic so almost every aspect of the survey (questions, answer choices, pages, groupings, etc...) is stored in the database and can be easily changed using an Access ADP front-end. The survey will be taken by college students all over the world and their responses also will be stored in the database.

    The people analyzing the data plan to use some statistical analysis software (SPSS). They need to have the responses presented in a "Spreadsheet" format, rather than a normalized raw-data format.

    For instance, they need the ResponderID (and other info - such as Language, start time, etc...) to appear as Row Headings, Question Numbers as Column Headings and Response value as Page data. (There's no need for aggregate functions - just the raw response value).

    I considered using OLAP with SQL Server to do this, but quickly found that it was far heavy for such a simple task. I also looked at importing the data into an Excel pivot table. However, exporting the results of the pivot table would be impractical to automate since the number of rows is variable.

    My current solution is to have a separate Access MDB database (note that the front-end is ADP) linking to the appropriate data source on SQL Server, with a Crosstab Query arranging the data into the perfect format. From there, the users can export the results to a format that will work with SPSS.

    Does that sound like the best approach under the circumstances?

    Thanks in advance for any feedback <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivoting Data - Best method? (2000/SQL Server 2000)

    Creating a crosstab query in a separate Access database sounds OK. There is no need to export it from Access. Recent versions of SPSS can import tables and queries from Access databases directly (File/Open Database/New Query... in SPSS 11). The import uses ODBC, so you shouldn't use Access-specific functions in the crosstab query.

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivoting Data - Best method? (2000/SQL Server 2000)

    Hi Hans,

    Thanks for your response. That's exactly what I was hoping to hear.

    I've never worked with SPSS - that's the last piece of the puzzle for me on this project. I'm glad to hear that it can import directly from ODBC. Not to worry - I don't have any functions at all. It's just a simple SELECT .... FROM .... PIVOT... statement to grab and display field values.

    Thanks again for your help! <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Pivoting Data - Best method? (2000/SQL Server 2000)

    FYI, you can also do some of those things in SQL Server, though the user interface isn't as friendly.
    Wendell

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivoting Data - Best method? (2000/SQL Server 2000)

    Oooo - I didn't realize that.

    I did see an article on SQL Team's website that talked about dynamically creating fields (Column headings), and populate it with the desired data (Row headings) using a nasty little Stored Procedure... Is this what you mean?

    Thanks

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Pivoting Data - Best method? (2000/SQL Server 2000)

    There are actually several ways to do it. The most straight forward is probably the PIVOT keyword in Transact SQL - search Books Online with that keyword. But it can also be done with the CASE statement in a simple select, and the CUBE and ROLLUP options in SELECT can also be used. The example you found looks pretty complex! But Access is still the most user friendly.
    Wendell

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivoting Data - Best method? (2000/SQL Server 2000)

    Hi Wendell,

    The CASE statement looks like a solid way, but the problem is that the number of questions is dynamic and will change for each survey.

    I think I discovered something funny about the PIVOT statement. I use SQL 7.0 on my development machine. The Production environment has SQL 2000. I'll bet the PIVOT keyword isn't supported in SQL 7 because I did try it at one point and discovered that it isn't supported. I guess they added it for SQL 2000. How interesting!

    I'll take a look at CUBE and ROLLUP for future reference.

    Thanks for your valuable insights!

Posting Permissions

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