Separating results from 1 column

• kellyfelz

  SSC Rookie

  Points: 25

  More actions

  May 13, 2015 at 8:35 am

  #303855

  Hello All,

  I am new to big SQL queries and have written a query I was SO proud of but THEN was told to separate the values out of one column. Would anyone be able to tell me a simple way to do this without having to rewrite the whole query? Will I need to include a Case statement?

  Basically the column contains several types of vacation codes which will need to be renamed to 2. So there is leave 1, leave 2, and leave 3 and sick. I will need to rename all leave 1, 2, and 3 to just vacation and leave sick as sick. Any thoughts?

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  May 13, 2015 at 8:47 am

  #1797571

  Without seeing your data, I'd be just guessing. But I think you need a splitter function. Search for that and see whether you find anything that helps.

  John

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  May 13, 2015 at 9:58 am

  #1797600

  First, welcome to SQL Server Central! See the link in my signature line to the article on how to best ask questions here. The small amount of time it takes to read that article will help make SQL Server Central an excellent place to get help. I've put together some sample data that I think looks like what you are working with.

  IF OBJECT_ID('tempdb..#yourtable') IS NOT NULL DROP TABLE #yourtable

  GO

  CREATE TABLE #yourtable (someid int identity primary key, col2 varchar(100) not null)

  GO

  INSERT #yourtable(col2) VALUES ('leave 1'),('sick'),('leave 2'),('leave 3'),('sick');

  -- review the data

  SELECT *

  FROM #yourtable;

  This may be what you are looking for:

  -- query to return only "vacation" or "sick" based on the column

  SELECT

  col2 =

  CASE

  WHEN col2 IN ('leave 1', 'leave 2','leave 3') THEN 'Vacation'

  ELSE col2

  END

  FROM #yourtable;

  The other option is to update the source data like so:

  -- query to update the source data

  UPDATE #yourtable

  SET col2 = 'Vacation'

  WHERE col2 IN ('leave 1', 'leave 2','leave 3');

  Then no case statement is needed.

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  May 13, 2015 at 10:08 am

  #1797604

  Have a look at the REPLACE function in Books Online (SQL Server help)

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