Combine rows into columns in SQL Server in a particular way-CodePudding

I don't know how to describe my problem, but I'll do my best. I'm working in SQL Server 2014. I've simplified the problem as much as I can since I'm working with sensitive info.

I currently have a query that returns the following from a table of test answers:

test_id	question_id	is_checked
1	1	TRUE
1	2	TRUE
1	3	FALSE
1	4	FALSE
2	1	FALSE
2	2	FALSE
2	3	FALSE
2	4	TRUE
3	1	FALSE
3	2	FALSE
3	3	FALSE
3	4	FALSE

Each test has only 4 yes/no questions (and this is unlikely to ever change). For each test, one or more questions can be marked yes. Above...

test 1 has questions 1 and 2 as yes, the rest as no.
test 2 has question 4 as yes, the rest as no.
test 3 has all questions marked as no.

I want my results to look like this:

test_id	question_1	question_2	question_3	question_4
1	TRUE	TRUE	FALSE	FALSE
2	FALSE	FALSE	FALSE	TRUE
3	FALSE	FALSE	FALSE	FALSE

I tried to use PIVOT to no luck. Any help would be appreciated, and I'm happy to provide more info.

EDIT:

My attempt at using PIVOT (please forgive my likely horrible formatting):

SELECT * 
FROM (
    SELECT test_id, question_id, is_checked FROM example_table
) as sourcetable 
pivot(
    any(is_checked) 
    for question_id 
    in (question_1, question_2, question_3, question_4)
) as pivottable

Populating an example table based on the above:

CREATE TABLE example_table (test_id int, question_id int, is_checked bit);

INSERT INTO example_table (test_id, question_id, is_checked)
VALUES
    ('1', '1', '1'),
    ('1', '2', '1'),
    ('1', '3', '0'),
    ('1', '4', '0'),
    ('2', '1', '0'),
    ('2', '2', '0'),
    ('2', '3', '0'),
    ('2', '4', '1'),
    ('3', '1', '0'),
    ('3', '2', '0'),
    ('3', '3', '0'),
    ('3', '4', '0');

Finally, my SQL Server version is SQL Server 2014. I previously put SQL Server 17 above, but have corrected it.

FINAL EDIT:

The column is_checked is a bit type in my system, but someone must have set it to output TRUE and FALSE when queried. In the answer below, I replaced is_checked with CAST(is_checked AS INT) and that worked.

CodePudding user response：

Please try the following solution.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (test_ID INT, question_id INT, is_checked VARCHAR(5));
INSERT INTO @tbl (test_ID, question_id, is_checked) VALUES
(1, 1, 'TRUE'),
(1, 2, 'TRUE'),
(1, 3, 'FALSE'),
(1, 4, 'FALSE'),
(2, 1, 'FALSE'),
(2, 2, 'FALSE'),
(2, 3, 'FALSE'),
(2, 4, 'TRUE'),
(3, 1, 'FALSE'),
(3, 2, 'FALSE'),
(3, 3, 'FALSE'),
(3, 4, 'FALSE');
-- DDL and sample data population, end

SELECT  test_ID
   , MAX(IIF(question_id = 1, is_checked, '')) AS question_1
   , MAX(IIF(question_id = 2, is_checked, '')) AS question_2
   , MAX(IIF(question_id = 3, is_checked, '')) AS question_3
   , MAX(IIF(question_id = 4, is_checked, '')) AS question_4
FROM @tbl
GROUP BY test_ID
ORDER BY test_ID;

Output

 --------- ------------ ------------ ------------ ------------ 
| test_ID | question_1 | question_2 | question_3 | question_4 |
 --------- ------------ ------------ ------------ ------------ 
|       1 | TRUE       | TRUE       | FALSE      | FALSE      |
|       2 | FALSE      | FALSE      | FALSE      | TRUE       |
|       3 | FALSE      | FALSE      | FALSE      | FALSE      |
 --------- ------------ ------------ ------------ ------------