Split Values

Question

Split Values

vani_r14

SSCrazy

Points: 2843
More actions
March 21, 2012 at 11:38 pm

#98453

Hi all
i have a requirement where one value has to be split into two if it meets a requirement.
e.g. When Course = 'Physical Training' then 'PT1', 'PT2' else Course end as Course_Id
Have tried using Case but am not getting anywhere.
Is there any way of doing this.
Please help
thanks
Vani

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Are you trying to use this? From the small fragment provided you are not going to get very good answers, just shots in the dark.

vani_r14 SSCrazy Points: 2843 More actions · Answer 2

Hi

There is one course that needs to be split into two if it was completed

Before a certain date.

Course Id - physical training if completed before 1/10/2010 needs

To be split into pt1 and pt2

select empid, courseid, case when courseid = 'physical training ' and enddate = '2012-10-01 00.000.00' then 'pt1' , 'pt2' end as crsid

[\code]

I need the data in two rows so the first row will have pt1, pt2 by emp no

Please help

Thanks

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

You could use a table variable to store the "split" values and join it to your table:

-- SETUP. When posting on the forums you'd better include a table definition like this

DECLARE @testData TABLE (

empid int,

courseid varchar(50),

enddate datetime

)

-- Sample data to test your query.

-- Next time you post a question, provide us a sample of your data.

INSERT INTO @testData VALUES (1, 'physical training', '2012-10-01 00:00.000') -- SPLIT

INSERT INTO @testData VALUES (2, 'physical training', '2012-10-02 00:00.000')

INSERT INTO @testData VALUES (3, 'physical training', '2012-10-03 00:00.000')

INSERT INTO @testData VALUES (4, 'physical training', '2012-10-01 00:00.000') -- SPLIT

-- Declare a table variable...

DECLARE @splitter TABLE (

i int,

v varchar(10)

)

-- ... and fill it with your "splits"

INSERT INTO @splitter

SELECT 1, 'pt1' -- SPLIT

UNION ALL

SELECT 1, 'pt2' -- SPLIT

UNION ALL

SELECT 0, NULL -- NO SPLIT

-- Final query:

SELECT empid

,courseid

,splitter.v AS crsid

FROM @testData AS testData

INNER JOIN @splitter AS splitter

ON i =

CASE

WHEN courseid = 'physical training '

AND enddate = '2012-10-01 00:00.000'

THEN 1

ELSE 0

END

If you don't like the table variable, you could use an inline non-correlated subquery or a CTE.

Hope this helps

Gianluca

-- Gianluca Sartori

vani_r14 SSCrazy Points: 2843 More actions · Answer 4

Hi Gianluca

Thank you for your help. That was great. I got it going.

cheers

vani

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

spaghettidba

SSC Guru

Points: 105732

March 26, 2012 at 2:31 am

#1464112

Great! Glad I could help.

-- Gianluca Sartori