Show rows as columns

• Deepthy

  SSCommitted

  Points: 1823

  More actions

  December 1, 2011 at 10:51 am

  #248931

  Hi everyone,

  I have the following table

  create table temp (status int,name varchar(20))

  insert table temp values(4,'with applicant')

  insert table temp values(3'in review')

  insert table temp values(3,'in review')

  insert table temp values(4,'with applicant')

  insert table temp values(4,'with applicant')

  insert table temp values(4,'with applicant')

  insert table temp values(4,'with applicant')

  insert ttable emp values(1,'cancelled')

  I want to show up my results as

  create table temp2(inreview varchar(20),withapplicant varchar(20),cancelled varchar(20))

  insert temp2 values (2,4,1)

  -- count of names

  Any help is really appreciated

• SQLback

  SSCrazy

  Points: 2897

  More actions

  December 1, 2011 at 11:27 am

  #1415472

  You can use Pivot function to do this..

  SELECT [in review] as 'inreview',[with applicant] as 'withapplicant',[cancelled] as 'cancelled'

  FROM

  (SELECT [status],[name]

  FROM temp) AS SourceTable

  PIVOT

  (

  Count([status])

  FOR [name] IN ([with applicant],[in review],[cancelled])

  ) AS PivotTable;

  For more information checkout this link http://msdn.microsoft.com/en-us/library/ms177410.aspx

• Ben Teraberry

  SSChampion

  Points: 11845

  More actions

  December 1, 2011 at 11:32 am

  #1415475

  You can also do a crosstab which tend to work better as the queries get more complex ...

  selectinReview = sum(case when name = 'in review' then 1 end)

  ,withApplicant = sum(case when name = 'with applicant' then 1 end)

  ,cancelled = sum(case when name = 'cancelled' then 1 end)

  from @temp;

  And btw, in your example there are 5 'with applicant' entries, not 4.

  └> bt
  

  ---

  
  Forum Etiquette: How to post data/code on a forum to get the best help[/url]

• Deepthy

  SSCommitted

  Points: 1823

  More actions

  December 1, 2011 at 2:07 pm

  #1415580

  Thanks both. That helped