Ndtyjky

Hi my table structure is shown as below

enter image description here

and with sql query I want to make it as below structure format

enter image description here

I have to make this with single sql query.

Currently I had made this with excel feature
Can I get any suggestion?

Questionid  Response        Response

   1        HighlyEngaged   HighlyEngaged

   2        VeryPrepared    VeryPrepared

   2        VeryPrepared1   VeryPrepared1



 to



 RowLabels        Count of Response

  1                1

  HighlyEngaged    1

  2                2 

  VeryPrepared     1

  VeryPrepared1    1

I have prepared following query, I think it can help you :

DROP TABLE QA

GO

CREATE TABLE QA

(

    Questionid  INT

    ,Response   VARCHAR(100) 

)



INSERT INTO QA

    VALUES(1,'HighlyEngaged')

   ,(2,'VeryPrepared' )

   ,(5,'Asked' )

   ,(5,'Priority' )

   ,(5,'Explained' )

   ,(8,'Yes' )

   ,(9,'Set Agenda' )

   ,(9,'Take Atten' )

   ,(11,'Assigned')

   ,(11,'Individual')

   ,(12,'Predict')

   ,(12,'Questions')



SELECT

    CASE

        WHEN Response = '' THEN CAST(QuestionId AS VARCHAR)

        ELSE ''

    END QId

   ,Response

   ,ResponseTotal

FROM (SELECT

        QuestionId

       ,'' Response

       ,COUNT(Response) ResponseTotal

    FROM QA

    GROUP BY QuestionId

    UNION ALL

    SELECT

        QuestionId

       ,Response

       ,COUNT(1)

    FROM QA

    GROUP BY QuestionId

            ,Response) a

ORDER BY QuestionId, CASE

    WHEN Response = '' THEN 0

    ELSE 1

END

drop table #teee

CREATE TABLE #teee

    ([Questionid] int, [Response] varchar(13), [Response1] varchar(13))

;



INSERT INTO #teee

    ([Questionid], [Response], [Response1])

VALUES

    (1, 'HighlyEngaged', 'HighlyEngaged'),

    (2, 'VeryPrepared', 'VeryPrepared'),

    (2, 'VeryPrepared1', 'VeryPrepared1')

;



select res,cnt from (select [Questionid],cast([Questionid]as varchar(100)) res ,count([Response]) cnt from #teee

group by [Questionid]

union all

select [Questionid],cast([Response]as varchar(100)) res,count([Response]) r1 from #teee

group by [Questionid],[Response])a

order by [Questionid],res

the following is an update for the answer given by Yogesh Sharma

select isnull([Response],[Questionid]),total from (select [Questionid], [Response], count(*) total

from #teee t  

group by [Questionid], [Response] with rollup) a

where  isnull([Response],[Questionid]) is not null

order by [Questionid],1 

You can use roll up with aggregation :

select questionid, Response, count(*)

from table t  

group by questionid, Response with roll up;