Pivot or CrossTab problem

• Felipe Brito

  Valued Member

  Points: 68

  More actions

  November 17, 2006 at 1:36 pm

  #115358

  To whom it may help.

  I have the following table structure

  table ENC_ANSWERS

  IDQ int 'Question ID

  IDP int 'Persons ID

  ANS nvarchar(800) 'ANSWER to the Question

  for example, there are two questions:

  IDQ 1: Gender? (Options are Male/Female)

  IDQ 2: Where do you use your lapton the most? (Options are Home, Office, University, HotSpots)

  I have a table with all records fill up.

  I need to know in a quick way, how many users answer for example 'Male' to IDQ 1 and Office to IDQ 2.

  As IDQ 1 and IDQ2 are two different records, I cannot find a simple way to get the number of records.

   

  I have tried all pivot statements with no positive results.

  anyone have any ideas o how this can be done in T-SQL???

  Thanks in advance

  Felipe Brito

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  November 17, 2006 at 1:52 pm

  #672749

  Try this:

  select count(*) from (

  select

      a.IDP,

      a.IDQ,

      b.IDQ

  from

      dbo.ENC_ANSWERS a

      inner join dbo.ENC_ANSWERS b

          on (a.IDP = b.IDP)

  where

      a.IDQ = 'Male'

      and b.IDQ = 'Office'

  ) t

   

  hth