Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Mar 2004
    Posts
    84

    Unanswered: Survey Statistic Query

    Is there a possibility to express the text column with SELECT SQL command, to get AS column which will represent all values in the field?

    I mean - to convert:


    IDresult__IDquestion_answers
    ....
    24______72_________178,180,191
    25______77_________221,224
    26______79_________291,298,301,302
    27______84_________522,523,525
    ....


    into:


    IDresult__IDquestion_numberedanswers
    ....
    24______72_________178
    24______72_________180
    24______72_________191
    25______77_________221
    25______77_________224
    26______79_________291
    26______79_________298
    26______79_________301
    26______79_________302
    27______84_________522
    27______84_________523
    27______84_________525
    ....


    Can SQL do that?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, i can think of one way to do it with just sql

    what is the range of numbers? are they all 3-digit numbers like 178,221,525, etc.?

    what are the lowest and highest numbers?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2004
    Posts
    84
    Actually, numbers represent IDanswers (from tbl_answers table). The range may be from 1 to .......

    If visitor checks three of five checkboxes, means, for example, that visitor checks 123,124,126 out of 123,124,125,126,127, means first, second and fourth checkbox. In table tbl_responses there will be 123,124,126 as string (CSV), and that's to be linked with tbl_answers, cause the tbl_answers has to be filtered using the string (CSV).

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so you actually want to join this table to the answers table?

    please show table layouts
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2004
    Posts
    84
    For example:

    tbl_questions
    IDquestion (primary)
    question (textual question)

    tbl_answers
    IDanswer (primary)
    IDquestion (foreign,to table tbl_questions)
    answer (textual answer)

    tbl_results
    IDresult (primary)
    IDquestion (foreign, to tbl_question)
    result (string based on CSV, may contain few IDanswers, because visitor may choose few checkboxes, for example it can be "4,6,7")

    Example:
    tbl_questions
    IDquestion_question
    1_________Which OS do you use?
    2_________Which databases do you use?

    tbl_answers
    IDanswer_IDquestion_answer
    1________1_________Windows
    2________1_________Linux
    3________1_________Mac OS
    4________2_________SQL Server
    5________2_________MS Access
    6________2_________MySQL
    7________2_________Oracle
    8________2_________Other
    9________2_________No, I do not use databases

    tbl_results
    IDresult_IDquestion_results
    ........
    23_______1________1,2
    24_______2________4,6,7
    ........

    Result of the query would be:
    IDquestion_IDanswer_answer
    1_________1________Windows
    1_________1________Linux
    2_________4________SQL Server
    2_________6________MySQL
    2_________7________Oracle

    Further queries based on this query should be able to make statistics (GROUP BY).

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select R.IDresult
         , R.IDquestion
         , A.IDanswer
         , A.answer
      from tbl_results as R
    inner
      join tbl_answers as A  
        on ',' + R.results + ',' 
         like '%,' + A.IDAnswer + ',%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2004
    Posts
    84
    Hmmm....

    ID is bigint, and if primary it's also identity field. That obviously makes errors.

    May I ask you to make an explanation, just for last two rows?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    those last two lines of the query allow this row --

    IDresult_IDquestion_results
    23_______1________1,2

    to join to both of these rows --

    IDanswer_IDquestion_answer
    1________1_________Windows
    2________1_________Linux

    BIGINT won't cause errors
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Mar 2004
    Posts
    84
    Well, I put it there, run SQL and I got BIGINT error.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    would you mind reproducing the exact error message?

    also, why BIGINT? how many quadrillion rows were you planning to have?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Mar 2004
    Posts
    84


    Okay, I do not plan to have an enormous number of records, but is there a big difference if it's tinyint, int or bigint?

    Cannot reproduce the error message now (in my timezone it's 3.15AM at the moment, I am going to take some "inspiration", recharge my batteries).

    Early in the morning I'll start the code at SQL Server, post the error message....

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    tinyint --> max 255
    integer --> max 2,147,483,647
    bigint --> max 9,223,372,036,854,775,807
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Mar 2004
    Posts
    84
    Well, my idea about the (fact) difference was not about the range of numbers any type can represent, but about the place inside the sql statement - those three types are numbers, and sql statement WHERE IN refers to scope.

  14. #14
    Join Date
    Mar 2004
    Posts
    84
    Server: Msg 8114, Level 16, State 5, Line 2
    Error converting data type varchar to bigint.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    would you kindly script out the tables please, so that we can see the datatypes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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