How to merge two rows into one (conditions or Pivot)

• shezi

  SSCommitted

  Points: 1514

  More actions

  July 8, 2015 at 12:59 pm

  #298443

  Merge two rows into one (conditions or Pivot?)

  I have Temptable showing as

  Columns:EmpName, Code, Balance

  Rows1: EmpA, X, 12

  Rows2: EmpA, Y, 10

  I want to insert the above temp table to another table with column names defined below like this

  Empname, Vacation Hours, Sicks Hours

  EmpA, 12, 10

  Basically if it is X it is vacation hours and if it is Y it is sick Hours. Needs a simple logic to place the apprpriate hours(Balance) to its respective columns. I'm not sure how I can achieve this in using Pivot or Conditions.

• WayneS

  SSC Guru

  Points: 95460

  More actions

  July 8, 2015 at 1:25 pm

  #1811118

  shezi (7/8/2015)

  ---

  Merge two rows into one (conditions or Pivot?)

  I have Temptable showing as

  Columns:EmpName, Code, Balance

  Rows1: EmpA, X, 12

  Rows2: EmpA, Y, 10

  I want to insert the above temp table to another table with column names defined below like this

  Empname, Vacation Hours, Sicks Hours

  EmpA, 12, 10

  Basically if it is X it is vacation hours and if it is Y it is sick Hours. Needs a simple logic to place the apprpriate hours(Balance) to its respective columns. I'm not sure how I can achieve this in using Pivot or Conditions.

  I think that this will work for you:

  SELECT Empname,

  MAX(CASE WHEN Code = 'X' THEN Balance ELSE NULL END) as [Vacation Hours],

  MAX(CASE WHEN Code = 'Y' THEN Balance ELSE NULL END) as [Sick Hours]

  INTO #TempTable2

  FROM #TempTable1

  GROUP BY Empname;

  Wayne
  Microsoft Certified Master: SQL Server 2008
  Author - SQL Server T-SQL Recipes

  ---

  If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

  ---

  Links:
  For better assistance in answering your questions
  Performance Problems
  Common date/time routines
  Understanding and Using APPLY Part 1 & Part 2

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  July 8, 2015 at 2:06 pm

  #1811133

  If for any reason there were multiple lines for the same employee for the same code, you might want a SUM instead, assuming all such rows are valid ... as follows:

  SELECT Empname,

  SUM(CASE Code WHEN 'X' THEN Balance ELSE NULL END) as [Vacation Hours],

  SUM(CASE Code WHEN 'Y' THEN Balance ELSE NULL END) as [Sick Hours]

  INTO #TempTable2

  FROM #TempTable1

  GROUP BY Empname;

  If multiple lines per employee and code combo would be an invalid scenario, but it occurs anyway, neither of these queries will guarantee an accurate result.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• shezi

  SSCommitted

  Points: 1514

  More actions

  July 10, 2015 at 2:36 pm

  #1811763

  works great..thanks!