Changing query result from column to lines

• streetsamurai

  Newbie

  Points: 8

  More actions

  June 17, 2010 at 7:44 pm

  #387182

  HI

  I'm trying to change the result from a request from a column to a lines.

  Here's an exanple of what i'm trying to do.

  The result of my request gives something like this

  company_id......Company_name........question_id........answer

  ........1.................Sony.................1.................yes

  ........1.................Sony.................2.................3

  ........1.................Sony.................3.................QC

  ........2.................CBC..................1.................no

  ........2.................CBC..................2.................4

  ........2.................CBC..................3.................QC

  I'd like the result to show like this

  company_id....Company_name.....answer1....answer2...answer3

  ........1...............Sony..............yes............3...........QC

  ........2...............CBC...............no.............4...........QC

  Thanks

• Dan.Humphries

  SSChampion

  Points: 12125

  More actions

  June 18, 2010 at 12:50 pm

  #1182320

  It is very likely that this is over simplyfied based on the example. If there are 100 questions I do not recommend doing a sub query for each, however if there are only a few questions then it should do nicely.

  declare @sample table

  (company_id int,Company_name varchar(5),question_id int,answer varchar(3))

  insert into @sample (company_id,Company_name,question_id,answer) values(1,'Sony',1,'yes')

  insert into @sample (company_id,Company_name,question_id,answer) values(1,'Sony',2,'3')

  insert into @sample (company_id,Company_name,question_id,answer) values(1,'Sony',3,'QC')

  insert into @sample (company_id,Company_name,question_id,answer) values(2,'CBC',1,'no')

  insert into @sample (company_id,Company_name,question_id,answer) values(2,'CBC',2,'4')

  insert into @sample (company_id,Company_name,question_id,answer) values(2,'CBC',3,'QC')

  select a.company_id,a.Company_name,

  (select answer from @sample where question_id=1 and company_id=a.company_id) as Answer1,

  (select answer from @sample where question_id=2 and company_id=a.company_id) as Answer2,

  (select answer from @sample where question_id=3 and company_id=a.company_id) as Answer3

  from @sample a

  Dan

  If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.