Collapse multiple like fields into a single field

• Yorgs Neil

  SSC-Addicted

  Points: 499

  More actions

  May 22, 2008 at 6:31 pm

  #70071

  Hello ALL,

  If I have data such as

  COLUM_NAME RECSET

  WH_LOC1_CD N5

  WH_LOC2_CD N5

  WH_LOC3_CD N5

  WH_LOC4_CD N5

  WH_LOC5_CD N5

  What is the best SQL syntax if I want to end up with

  COLUM_NAME RECSET

  WH_LOC_CD N5

  Thanks 😎

• raja_saminathan

  SSCommitted

  Points: 1607

  More actions

  May 23, 2008 at 4:10 am

  #819570

  Hi,

  what about the Datas in each and EVERY Column while Combining?

  Do you want to concatenate all the datas in all columns into a single Column

  Can you examplain what would you like to do along with sample datas and expected Results

  Rajesh

• Yorgs Neil

  SSC-Addicted

  Points: 499

  More actions

  May 23, 2008 at 1:42 pm

  #819907

  COLUM_NAME RECSET

  WH_LOC1_CD N5

  WH_LOC2_CD N5

  WH_LOC3_CD N5

  WH_LOC4_CD N5

  WH_LOC5_CD N5

  I am really not conerned about the data.

  I am trying to create a NEW derived column name

  which is kind of a class or group name

  So in this case the name would collapse to WH_LOC_CD

  I believe I have to use a CHARINDEX function here but do not know the best way to handle index numbers lets say 1-20

  Thanks for the response 🙂

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  May 24, 2008 at 9:29 pm

  #820043

  OK, well this is ugly as sin, but it will work:

  Select

  Case When Min(substring(COLUM_NAME, 1, 1))

  = Max(substring(COLUM_NAME, 1, 1))

  Then Min(substring(COLUM_NAME, 1, 1))

  Else '' END

  +Case When Min(substring(COLUM_NAME, 2, 1))

  = Max(substring(COLUM_NAME, 2, 1))

  Then Min(substring(COLUM_NAME, 2, 1))

  Else '' END

  +Case When Min(substring(COLUM_NAME, 3, 1))

  = Max(substring(COLUM_NAME, 3, 1))

  Then Min(substring(COLUM_NAME, 3, 1))

  Else '' END

  +Case When Min(substring(COLUM_NAME, 4, 1))

  = Max(substring(COLUM_NAME, 4, 1))

  Then Min(substring(COLUM_NAME, 4, 1))

  Else '' END

  +Case When Min(substring(COLUM_NAME, 5, 1))

  = Max(substring(COLUM_NAME, 5, 1))

  Then Min(substring(COLUM_NAME, 5, 1))

  Else '' END

  +Case When Min(substring(COLUM_NAME, 6, 1))

  = Max(substring(COLUM_NAME, 6, 1))

  Then Min(substring(COLUM_NAME, 6, 1))

  Else '' END

  +Case When Min(substring(COLUM_NAME, 7, 1))

  = Max(substring(COLUM_NAME, 7, 1))

  Then Min(substring(COLUM_NAME, 7, 1))

  Else '' END

  +Case When Min(substring(COLUM_NAME, 8, 1))

  = Max(substring(COLUM_NAME, 8, 1))

  Then Min(substring(COLUM_NAME, 8, 1))

  Else '' END

  +Case When Min(substring(COLUM_NAME, 9, 1))

  = Max(substring(COLUM_NAME, 9, 1))

  Then Min(substring(COLUM_NAME, 9, 1))

  Else '' END

  +Case When Min(substring(COLUM_NAME, 10, 1))

  = Max(substring(COLUM_NAME, 10, 1))

  Then Min(substring(COLUM_NAME, 10, 1))

  Else '' END

  +Case When Min(substring(COLUM_NAME, 11, 1))

  = Max(substring(COLUM_NAME, 11, 1))

  Then Min(substring(COLUM_NAME, 11, 1))

  Else '' END

  +Case When Min(substring(COLUM_NAME, 12, 1))

  = Max(substring(COLUM_NAME, 12, 1))

  Then Min(substring(COLUM_NAME, 12, 1))

  Else '' END

  +Case When Min(substring(COLUM_NAME, 13, 1))

  = Max(substring(COLUM_NAME, 13, 1))

  Then Min(substring(COLUM_NAME, 13, 1))

  Else '' END

  +Case When Min(substring(COLUM_NAME, 14, 1))

  = Max(substring(COLUM_NAME, 14, 1))

  Then Min(substring(COLUM_NAME, 14, 1))

  Else '' END

  +Case When Min(substring(COLUM_NAME, 15, 1))

  = Max(substring(COLUM_NAME, 15, 1))

  Then Min(substring(COLUM_NAME, 15, 1))

  Else '' END

  +Case When Min(substring(COLUM_NAME, 16, 1))

  = Max(substring(COLUM_NAME, 16, 1))

  Then Min(substring(COLUM_NAME, 16, 1))

  Else '' END

  +Case When Min(substring(COLUM_NAME, 17, 1))

  = Max(substring(COLUM_NAME, 17, 1))

  Then Min(substring(COLUM_NAME, 17, 1))

  Else '' END

  +Case When Min(substring(COLUM_NAME, 18, 1))

  = Max(substring(COLUM_NAME, 18, 1))

  Then Min(substring(COLUM_NAME, 18, 1))

  Else '' END

  +Case When Min(substring(COLUM_NAME, 19, 1))

  = Max(substring(COLUM_NAME, 19, 1))

  Then Min(substring(COLUM_NAME, 19, 1))

  Else '' END

  +Case When Min(substring(COLUM_NAME, 20, 1))

  = Max(substring(COLUM_NAME, 20, 1))

  Then Min(substring(COLUM_NAME, 20, 1))

  Else '' END

  , RECSET

  From [tablename]

  Group by RECSET

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]