|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SELECT question - need helpI have two tables. The first has user information in the form: UserID | Fname | Lname | State | Zip 37 | Jim | Jones | NY | 19040 38 | Tom | Fredrick | PA | 88888 39 | Bill | Thomson | CA | 99999 The second has some survey information in the form: UserId | Answer | QuestionId 37 | Internet Direct, Inc. | 1 37 | Corporate | 2 37 | PCS-G70S $500.00, PCS-G70N $500.00, PCS-TL30 $100.00 | 3 37 | Yes | 4 38 | ABC Corp | 1 38 | Corporate | 2 38 | PCS-G50 $300.00, PCS-TL50 $100.00, PCS-TL30 $100.00 | 3 38 | Yes | 4 39 | MacDonalds | 1 39 | Corporate | 2 39 | PCS-1 $300.00 | 3 39 | No | 4 I would like to have a SELECT statement that formats the results in the following manner UserID | Fname | Lname | State | Zip | Company Name | Nature of Business | Products | Wants Email (Note that query should rename the QuestionID's as: 1 = 'Company Name' 2 = 'Nature of Business' 3 = 'Products' 4 = 'Wants Email') Been banging my head against this one all day. TIA
Show quote
"Dr Van Nostrand" <nob***@nowhere.com> wrote in message Why do you record data collected by survey as if it were some special news:uMIGPgr9FHA.1032@TK2MSFTNGP11.phx.gbl... > In SQL Server 2000: > > I have two tables. The first has user information in the form: > > UserID | Fname | Lname | State | Zip > 37 | Jim | Jones | NY | 19040 > 38 | Tom | Fredrick | PA | 88888 > 39 | Bill | Thomson | CA | 99999 > > > The second has some survey information in the form: > > UserId | Answer | QuestionId > 37 | Internet Direct, Inc. | 1 > 37 | Corporate | 2 > 37 | PCS-G70S $500.00, PCS-G70N $500.00, PCS-TL30 $100.00 | 3 > 37 | Yes | 4 > > 38 | ABC Corp | 1 > 38 | Corporate | 2 > 38 | PCS-G50 $300.00, PCS-TL50 $100.00, PCS-TL30 $100.00 | 3 > 38 | Yes | 4 > > 39 | MacDonalds | 1 > 39 | Corporate | 2 > 39 | PCS-1 $300.00 | 3 > 39 | No | 4 > > > > I would like to have a SELECT statement that formats the results in the > following manner > > UserID | Fname | Lname | State | Zip | Company Name | Nature of Business > | Products | Wants Email > > > (Note that query should rename the QuestionID's as: > 1 = 'Company Name' > 2 = 'Nature of Business' > 3 = 'Products' > 4 = 'Wants Email') > > Been banging my head against this one all day. > > TIA > > > > > category of data? This one always intrigues me when it comes up. Imagine you were building an HR database. Would you create question and answer columns with values like "What is your name?", answer "My name is John Smith" and "What is you social security number?", answer "123456789"? I'm sure not. My point is that the method you use to gather the data has nothing to do with the way it should be stored. How you get the survey into the database is a data integration problem which can be solved using pretty common tools and techniques. But it's a problem that ought to be solved well before you start writing reports against the data. Someone seems to have failed to do that properly here, since the data you are working with is clearly such a very poor design. Anyway, assuming you are stuck with this you'll apparently want a crosstab report. Any reporting tool will do that for you, or take a look at: http://www.aspfaq.com/show.asp?id=2462 -- David Portas SQL Server MVP -- > Why do you record data collected by survey as if it were some special This was the work of my not-too-brilliant predecessor. My SQL may be rusty > category of data? This one always intrigues me when it comes up. Imagine > you were building an HR database. Would you create question and answer > columns with values like "What is your name?", answer "My name is John > Smith" and "What is you social security number?", answer "123456789"? I'm > sure not. but I would have never arranged the data is such a wonky format. Thanks -- I'll check out the cross tab info. Without forgetting anything of what D. Portas said to you in is previous
message, if you exactly knew the number and titles associated with each QuestionID, then you can simply use multiple joins and add the QuestionId on the join: Select UI.UserId, ... , A1.Answer as CompanyName, A2.Answer as NatureOfBusiness, ... From UserInformation UI inner join Answer A1 on (A.UserId = UI.UserId and A.IdQuestion = 1) inner join Answer A2 on (A.UserId = UI.UserId and A.IdQuestion = 2) .... Replace the Inner Join with Left Outer Join if necessary. Show quote "Dr Van Nostrand" <nob***@nowhere.com> wrote in message news:uMIGPgr9FHA.1032@TK2MSFTNGP11.phx.gbl... > In SQL Server 2000: > > I have two tables. The first has user information in the form: > > UserID | Fname | Lname | State | Zip > 37 | Jim | Jones | NY | 19040 > 38 | Tom | Fredrick | PA | 88888 > 39 | Bill | Thomson | CA | 99999 > > > The second has some survey information in the form: > > UserId | Answer | QuestionId > 37 | Internet Direct, Inc. | 1 > 37 | Corporate | 2 > 37 | PCS-G70S $500.00, PCS-G70N $500.00, PCS-TL30 $100.00 | 3 > 37 | Yes | 4 > > 38 | ABC Corp | 1 > 38 | Corporate | 2 > 38 | PCS-G50 $300.00, PCS-TL50 $100.00, PCS-TL30 $100.00 | 3 > 38 | Yes | 4 > > 39 | MacDonalds | 1 > 39 | Corporate | 2 > 39 | PCS-1 $300.00 | 3 > 39 | No | 4 > > > > I would like to have a SELECT statement that formats the results in the > following manner > > UserID | Fname | Lname | State | Zip | Company Name | Nature of Business > | Products | Wants Email > > > (Note that query should rename the QuestionID's as: > 1 = 'Company Name' > 2 = 'Nature of Business' > 3 = 'Products' > 4 = 'Wants Email') > > Been banging my head against this one all day. > > TIA > > > > >  |
|||||||||||||||||||||||
Other interesting topics