-- DROP TABLE #Users;
-- DROP TABLE #Questions;
-- DROP TABLE #UserAnswers;
CREATE TABLE #Users (
UserID INT NOT NULL,
UserName VARCHAR(25) NOT NULL,
UserPassword VARCHAR(25) NOT NULL,
CreateDate DATETIME NOT NULL
);

CREATE TABLE #Questions (
QuestionID INT NOT NULL,
QuestionText VARCHAR(50) NOT NULL
);

CREATE TABLE #UserAnswers (
UserID INT NOT NULL,
QuestionID INT NOT NULL,
Answer VARCHAR(100) NOT NULL
);

INSERT #Users VALUES (1, 'User101', 'abc123', CURRENT_TIMESTAMP );

INSERT #Questions VALUES(1, 'FirstName');
INSERT #Questions VALUES(2, 'LastName');
INSERT #Questions VALUES(3, 'Address');
INSERT #Questions VALUES(4, 'City');
INSERT #Questions VALUES(5, 'State');

INSERT #UserAnswers VALUES(1, 1, 'John');
INSERT #UserAnswers VALUES(1, 3, '111 Main Street');
INSERT #UserAnswers VALUES(1, 4, 'New York');
INSERT #UserAnswers VALUES(1, 5, 'NY');

SELECT u.UserID , u.UserName , u.UserPassword , q.QuestionText , a.Answer
FROM #Users AS u
JOIN #UserAnswers As a
ON u.UserID = a.UserID
JOIN #Questions q
ON q.QuestionID = a.QuestionID
ORDER BY u.UserName , q.QuestionID;
/*
UserID      UserName   UserPassword  QuestionText  Answer                                                                                               
----------- ---------- ------------- ------------- ----------------
          1 User101    abc123        FirstName     John
          1 User101    abc123        Address       111 Main Street
          1 User101    abc123        City          New York
          1 User101    abc123        State         NY
*/
-- Expected Output:
/*
UserID      UserName   UserPassword  FirstName  LastName  Address           City      State                                                                                                
----------- ---------- ------------- ---------- --------- ----------------- --------- ------
          1 User101    abc123        John       NULL      111 Main Street   New York  NY
*/
-- This SELECT produces the above output but the column values have to be hard-coded.
SELECT u.UserID , u.UserName , u.UserPassword ,
       MIN( CASE q.QuestionText WHEN 'FirstName' THEN a.Answer END ) AS FirstName ,
       MIN( CASE q.QuestionText WHEN 'LastName' THEN a.Answer END ) AS LastName ,
       MIN( CASE q.QuestionText WHEN 'Address' THEN a.Answer END ) AS Address ,
       MIN( CASE q.QuestionText WHEN 'City' THEN a.Answer END ) AS City ,
       MIN( CASE q.QuestionText WHEN 'State' THEN a.Answer END ) AS State
FROM #Users AS u
JOIN #UserAnswers As a
ON u.UserID = a.UserID
JOIN #Questions q
ON q.QuestionID = a.QuestionID
GROUP BY u.UserID , u.UserName , u.UserPassword
ORDER BY u.UserName;

-- This SELECT statement produces a similar output but doesn't hard-code the 
-- actual values of the QuestionText but uses the ID values instead.
SELECT u.UserID , u.UserName , u.UserPassword ,
       MIN( CASE q.QuestionID WHEN 1 THEN q.QuestionText END ) AS Question1 ,
       MIN( CASE q.QuestionID WHEN 2 THEN q.QuestionText END ) AS Question2 ,
       MIN( CASE q.QuestionID WHEN 3 THEN q.QuestionText END ) AS Question3 ,
       MIN( CASE q.QuestionID WHEN 4 THEN q.QuestionText END ) AS Question4 ,
       MIN( CASE q.QuestionID WHEN 5 THEN q.QuestionText END ) AS Question5 ,
       MIN( CASE q.QuestionID WHEN 1 THEN a.Answer END ) AS Answer1 ,
       MIN( CASE q.QuestionID WHEN 2 THEN a.Answer END ) AS Answer2 ,
       MIN( CASE q.QuestionID WHEN 3 THEN a.Answer END ) AS Answer3 ,
       MIN( CASE q.QuestionID WHEN 4 THEN a.Answer END ) AS Answer4 ,
       MIN( CASE q.QuestionID WHEN 5 THEN a.Answer END ) AS Answer5
FROM #Users AS u
JOIN #UserAnswers As a
ON u.UserID = a.UserID
JOIN #Questions q
ON q.QuestionID = a.QuestionID
GROUP BY u.UserID , u.UserName , u.UserPassword
ORDER BY u.UserName;
/*
UserID      UserName  UserPassword  Question1  Question2  Question3  Question4  Question5 
----------- --------- ------------- ---------- ---------- ---------- ---------- ----------
          1 User101   abc123        FirstName  NULL       Address    City       State     

Answer1  Answer2  Answer3          Answer4   Answer5                                                                                              
-------- -------- ---------------- --------- --------
John     NULL     111 Main Street  New York  NY
*/
This page was last updated on May 01, 2006 04:28 PM.