Group Column Based On Anouther column

• kna114

  SSC Journeyman

  Points: 83

  More actions

  July 11, 2014 at 2:24 am

  #296434

  I have Table Like this

  t_id w_id t_codew_name

  358553680A1100EVM Method Project

  358563680A1110EVM Method Project

  358453684A1000Basic

  358463684A1010Basic

  358473685A1020Detail

  358483686A1030Purchase Order

  358493686A1040Purchase Order

  358503686A1050Purchase Order

  358513687A1060Delivery To site

  358523688A1070Civil

  358533689A1080Installation

  358543689A1090Installation

  Bu I want to Like This

  wbs_nameTest_c

  EVM Method Project

  NullA1100

  NullA1110

  Basic

  NullA1000

  NullA1010

  Detail

  NullA1020

  Purchase Order

  NullA1030

  NullA1040

  NullA1050

  Delivery To site

  NullA1060

  Civil

  NullA1070

  Installation

  NullA1080

  NullA1090

  Can Anyone help Me?

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  July 11, 2014 at 3:16 am

  #1728645

  CREATE TABLE #Sample (t_id int, w_id int, t_code CHAR(5), w_name VARCHAR(25))

  INSERT INTO #Sample (t_id, w_id, t_code, w_name)

  SELECT 35855, 3680, 'A1100', 'EVM Method Project' UNION ALL

  SELECT 35856, 3680, 'A1110', 'EVM Method Project' UNION ALL

  SELECT 35845, 3684, 'A1000', 'Basic' UNION ALL

  SELECT 35846, 3684, 'A1010', 'Basic' UNION ALL

  SELECT 35847, 3685, 'A1020', 'Detail' UNION ALL

  SELECT 35848, 3686, 'A1030', 'Purchase Order' UNION ALL

  SELECT 35849, 3686, 'A1040', 'Purchase Order' UNION ALL

  SELECT 35850, 3686, 'A1050', 'Purchase Order' UNION ALL

  SELECT 35851, 3687, 'A1060', 'Delivery To site' UNION ALL

  SELECT 35852, 3688, 'A1070', 'Civil' UNION ALL

  SELECT 35853, 3689, 'A1080', 'Installation' UNION ALL

  SELECT 35854, 3689, 'A1090', 'Installation'

  SELECT

  wbs_name = CASE WHEN rn = 1 THEN NULL ELSE w_name END,

  Test_c = CASE WHEN rn = 0 THEN '' ELSE t_code END

  FROM (

  SELECT w_id, t_code, w_name, rn = 1

  FROM #Sample

  UNION ALL

  SELECT w_id, NULL, MAX(w_name), rn = 0

  FROM #Sample

  GROUP BY w_id

  ) d

  ORDER BY w_id, rn, t_code

  ^{“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