How to pivot this content?

Question

How I should PIVOT my result contents? What's wrong in my current query as its returning null?

select 'ClientResponse' As Item,
[0],[1],[2],[3],[4],[5],[6],[7]
FROM (
SELECT ClientID, ClientResponse   
FROM TestResponses where ClientID = 116 and TestID = 16) As SourceTable  
PIVOT  
(  
 MAX(ClientResponse)  
 FOR ClientID IN ([0], [1], [2], [3], [4],[5],[6],[7])  
) AS PivotTable; 

Where my source table is this

And my code snippet is returning like this

Answer 1

The values of column ClientID doesn't match with the column names of the pivot ([0], [1], ...).

But you can use the following, using a DENSE_RANK to create a group number (starting on 1):

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
    WHERE ClientID = 116 AND TestID = 16
) AS st PIVOT (  
    MAX(ClientResponse)  
    FOR groupNum IN ([1],[2], [3], [4], [5], [6], [7])  
) AS pt; 

demo on dbfiddle.uk

Answer 2

declare @maxColumnCount int=0;
declare @Query varchar(max)='';
declare @DynamicColumnName nvarchar(MAX)='';

-- table type variable that store all values of column row no
DECLARE @TotalRows TABLE( row_count int)
INSERT INTO @TotalRows (row_count)
SELECT (ROW_NUMBER() OVER(PARTITION BY ClientID order by TestResponseID asc)) as 
row_no FROM TestResponses where ClientID = 116 and TestID = 16

-- Get the MAX value from @TotalRows table
set @maxColumnCount= (select max(row_count) from @TotalRows)
select * from @TotalRows 
-- loop to create Dynamic max/case and store it into local variable 
DECLARE @cnt INT = 1;
 WHILE @cnt <= @maxColumnCount
   BEGIN
      set @DynamicColumnName= @DynamicColumnName + ', Max(case when row_no= 
 '+cast(@cnt 
   as varchar)+' then ClientResponse end )as Item_'+cast(@cnt as varchar)+''
   SET @cnt = @cnt + 1;
END;

  -- Create dynamic CTE and store it into local variable @query 
  set @Query='
     with CTE_tbl as
     (
       SELECT ClientID,ClientResponse,
       ROW_NUMBER() OVER(PARTITION BY ClientID order by TestResponseID asc) as row_no
       FROM TestResponses where ClientID = 116 and TestID = 16
     )
 select
     ClientID
     '+@DynamicColumnName+'
    FROM CTE_tbl
    group By ClientID'
 --print @Query
-- Execute the Query
execute (@Query)

Answer 3

Hi if i understand what your're trying todo i think thoses example can be help you :

CREATE TABLE #ClientResponse (ClientId int, ClientResponse varchar(50))

INSERT INTO #ClientResponse
SELECT 116,'M'
UNION ALL 
SELECT 116,'M'
UNION ALL 
SELECT 116,'N'
UNION ALL 
SELECT 116,'J'
UNION ALL 
SELECT 116,'G'
UNION ALL 
SELECT 116,'M'
UNION ALL 
SELECT 116,'K'




select 'ClientResponse' As Item,
[0],[1],[2],[3],[4],[5],[6],[7]
FROM (SELECT *, ROW_NUMBER() OVER(ORDER by ClientId) AS 'IndexResponse' FROM #ClientResponse) As SourceTable  
PIVOT  
(  
 MAX(ClientResponse)  
 FOR IndexResponse IN ([0], [1], [2], [3], [4],[5],[6],[7])  
) AS PivotTable; 


DROP TABLE #ClientResponse

RESULT :