SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE TestResponses (
  ClientID INT,
  ClientResponse VARCHAR(20)
);
INSERT INTO TestResponses VALUES
  (116, 'A'),
  (116, 'Z'),
  (117, 'B'),
  (117, 'X');
4 rows affected

SELECT 'ClientResponse' AS Item, [1],[2], [3], [4], [5], [6], [7]
FROM (
    SELECT ClientResponse, 
        DENSE_RANK() OVER (ORDER BY ClientID ASC) AS groupNum
    FROM TestResponses
) AS st PIVOT (  
    MAX(ClientResponse)  
    FOR groupNum IN ([1],[2], [3], [4], [5], [6], [7])  
) AS pt; 


Item
1
2
3
4
5
6
7


ClientResponse
Z
X
null
null
null
null
null