Split Values

Question

Post reply

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: 442447 More actions · Answer 1

Lynn Pettis

SSC Guru

Points: 442447

March 22, 2012 at 12:12 am

#1462525

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: 105712 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