display month wise data in sql server

• asranantha

  SSCarpal Tunnel

  Points: 4246

  More actions

  January 18, 2015 at 8:05 am

  #314707

  Hi Friends I have small doubt in sql server please tell me how to resolve this in sql server

  Table data like below

  id |month|stat|count

  1 |1|admit|7

  2 |8|admit|47

  1 |7|admit|28

  2 |9|admit|11

  3 |12|dischr|4

  4 |10|openc|5

  1 |11|admit|1

  2 |6|admit|5

  2 |4|admit|8

  1 |3|dischr|10

  2 |2|admit|30

  3 |5|dischr|20

  1 |8|admit|13

  3 |8|dischr|1

  4 |9|admit|30

  2 |10|admit|20

  3 |10|deschr|20

  Based on this when month=1 then January and when month=2 then febuary similary upto 12 based on that condition I got output like below. using below query

  SELECT *

  FROM (

  SELECT case when month='1' then 'January'

  when month='2' then 'February'

  when month='3' then 'March'

  when month='4' then 'April'

  when month='5' then 'May '

  when month='6' then 'June'

  when month='7' then 'July'

  when month='8' then 'August'

  when month='9' then 'September'

  when month='10' then 'October'

  when month='11' then 'November'

  when month='12' then 'December'

  else month end Month,

  stat,count,id

  from month

  ) as s

  PIVOT

  (

  SUM(count)

  FOR [stat] IN (Admit, Dischr, OpenC)

  )b

  and i got output like below

  Month|id|Admit|Dischr|OpenC

  August|1|13|NULL|NULL

  January|1|7|NULL|NULL

  July|1|28|NULL|NULL

  March|1|NULL|10|NULL

  November|1|1|NULL|NULL

  April|2|8|NULL|NULL

  August|2|47|NULL|NULL

  February|2|30|NULL|NULL

  June|2|5|NULL|NULL

  October2|20|NULL|NULL

  September|2|11|NULL|NULL

  August|3|NULL|1|NULL

  December|3|NULL|4|NULL

  May |3|NULL|20|NULL

  October|3|NULL|NULL|NULL

  October|4|NULL|NULL|5

  September|4|30|NULL|NULL

  but i want output like month wise proper order and output look like below

  Month|id|Admit|Dischr|OpenC

  January|1|7|NULL|NULL

  February|2|30|NULL|NULL

  March|1|NULL|10|NULL

  April|2|8|NULL|NULL

  May |3|NULL|20|NULL

  June|2|5|NULL|NULL

  July|1|28|NULL|NULL

  August|1|13|NULL|NULL

  August|2|47|NULL|NULL

  August|3|NULL|1|NULL

  September|2|11|NULL|NULL

  September|4|30|NULL|NULL

  October|2|20|NULL|NULL

  October|3|NULL|NULL|NULL

  October|4|NULL|NULL|5

  November|1|1|NULL|NULL

  December|3|NULL|4|NULL

  please tell me query how to solve this issue in sql server.

• Jeff Moden

  SSC Guru

  Points: 1004405

  More actions

  January 18, 2015 at 12:34 pm

  #1771001

  asranantha (1/18/2015)

  ---

  Hi Friends I have small doubt in sql server please tell me how to resolve this in sql server

  Table data like below

  id |month|stat|count

  1 |1|admit|7

  2 |8|admit|47

  1 |7|admit|28

  2 |9|admit|11

  3 |12|dischr|4

  4 |10|openc|5

  1 |11|admit|1

  2 |6|admit|5

  2 |4|admit|8

  1 |3|dischr|10

  2 |2|admit|30

  3 |5|dischr|20

  1 |8|admit|13

  3 |8|dischr|1

  4 |9|admit|30

  2 |10|admit|20

  3 |10|deschr|20

  Based on this when month=1 then January and when month=2 then febuary similary upto 12 based on that condition I got output like below. using below query

  SELECT *

  FROM (

  SELECT case when month='1' then 'January'

  when month='2' then 'February'

  when month='3' then 'March'

  when month='4' then 'April'

  when month='5' then 'May '

  when month='6' then 'June'

  when month='7' then 'July'

  when month='8' then 'August'

  when month='9' then 'September'

  when month='10' then 'October'

  when month='11' then 'November'

  when month='12' then 'December'

  else month end Month,

  stat,count,id

  from month

  ) as s

  PIVOT

  (

  SUM(count)

  FOR [stat] IN (Admit, Dischr, OpenC)

  )b

  and i got output like below

  Month|id|Admit|Dischr|OpenC

  August|1|13|NULL|NULL

  January|1|7|NULL|NULL

  July|1|28|NULL|NULL

  March|1|NULL|10|NULL

  November|1|1|NULL|NULL

  April|2|8|NULL|NULL

  August|2|47|NULL|NULL

  February|2|30|NULL|NULL

  June|2|5|NULL|NULL

  October2|20|NULL|NULL

  September|2|11|NULL|NULL

  August|3|NULL|1|NULL

  December|3|NULL|4|NULL

  May |3|NULL|20|NULL

  October|3|NULL|NULL|NULL

  October|4|NULL|NULL|5

  September|4|30|NULL|NULL

  but i want output like month wise proper order and output look like below

  Month|id|Admit|Dischr|OpenC

  January|1|7|NULL|NULL

  February|2|30|NULL|NULL

  March|1|NULL|10|NULL

  April|2|8|NULL|NULL

  May |3|NULL|20|NULL

  June|2|5|NULL|NULL

  July|1|28|NULL|NULL

  August|1|13|NULL|NULL

  August|2|47|NULL|NULL

  August|3|NULL|1|NULL

  September|2|11|NULL|NULL

  September|4|30|NULL|NULL

  October|2|20|NULL|NULL

  October|3|NULL|NULL|NULL

  October|4|NULL|NULL|5

  November|1|1|NULL|NULL

  December|3|NULL|4|NULL

  please tell me query how to solve this issue in sql server.

  If you want an actual bit of tested code to solve your problem, please see how to correctly post readily consumable data by reading the article at the first link under "Helpful Links" in my signature line below.

  For a more generic solution that you could apply to your code, please see the following article that discusses and provides code examples for this problem.

  http://www.sqlservercentral.com/articles/T-SQL/71511/

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)