Binaryworld - Yet another pivot table example ... [ SQL Server -> T-SQL ]

-- 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
*/