pivot / unpivot

• amitkumarmailid

  Grasshopper

  Points: 11

  More actions

  February 19, 2014 at 1:45 am

  #285804

  hi...pls help...

  i have a table with data as :

  emp_codepaydatebasichramedsplall ....

  a00131-01-141000020001001000

  a00231-01-142000020001001000

  a00331-01-141500020001001000

  a00431-01-141000020001001000

  a00531-01-141000020001001000

  a00631-01-141000020001001000

  a00131-12-131000020001001000

  a00231-12-132000020001001000

  a00331-12-131500020001001000

  a00431-12-131000020001001000

  a00531-12-131000020001001000

  a00631-12-131000020001001000

  .

  .

  ...............................................................

  AND NOW I NEED OUTPUT IN GIVEN FORMAT :

  31-01-1431-12-13

  BASIC7500075000

  HRA 1200012000

  MED 600 600

  SPLALL6000 6000

  NOTE : SUM OF BASIC OF ALL EMP IN A MONTH 31-01-14: 75000

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  February 19, 2014 at 2:54 am

  #1690187

  Hi and welcome to ssc.

  There's an article for newcomers which you may find useful; http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  If you take a little time to prepare a sample data script, forum users will be encouraged to participate in your thread.

  This time I’ve prepared your sample data for you;

  -- Sample data setup

  SET DATEFORMAT DMY

  DROP TABLE #Sample

  CREATE TABLE #Sample (emp_code VARCHAR(4), paydate DATE, [basic] INT, hra INT, med INT, splall INT)

  INSERT INTO #Sample VALUES

  ('a001', '31-01-14', 10000, 2000, 100, 1000),

  ('a002', '31-01-14', 20000, 2000, 100, 1000),

  ('a003', '31-01-14', 15000, 2000, 100, 1000),

  ('a004', '31-01-14', 10000, 2000, 100, 1000),

  ('a005', '31-01-14', 10000, 2000, 100, 1000),

  ('a006', '31-01-14', 10000, 2000, 100, 1000),

  ('a001', '31-12-13', 10000, 2000, 100, 1000),

  ('a002', '31-12-13', 20000, 2000, 100, 1000),

  ('a003', '31-12-13', 15000, 2000, 100, 1000),

  ('a004', '31-12-13', 10000, 2000, 100, 1000),

  ('a005', '31-12-13', 10000, 2000, 100, 1000),

  ('a006', '31-12-13', 10000, 2000, 100, 1000)

  SELECT * FROM #Sample; -- check everything is ok (Stare & Compare)

  Here’s a solution which employs both CROSS APPLY VALUES [/url](a handy unpivot alternative championed by Dwain Camps) and CROSSTAB[/url] (championed by MVP Jeff Moden);

  -- Solution

  SELECT

  Category,

  [31-01-14] = SUM(CASE WHEN paydate = '31-01-14' THEN Amount END),

  [31-12-13] = SUM(CASE WHEN paydate = '31-12-13' THEN Amount END)

  FROM #Sample

  CROSS APPLY (

  VALUES

  ('basic', [basic]),

  ('hra', hra),

  ('med', med),

  ('splall', splall)

  ) x (Category, Amount)

  GROUP BY Category

  Test it rigorously. If there are any issues, post back, extending your sample data set if necessary to help explain your findings.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden