-- 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.