SQL Select with condition

  • Dee Dee-422077

    SSC Eights!

    Points: 930

    Hi,

    In my sql table,  there is a column which have string of text :

    Column

    2ABF   CD000000000012345000000000001156

    3000000000003452000000000000356

    I would like if the 1st character is 2 then  out put as

    ID               Code           Num1          Num2          Num3       Num4

    2ABF        CD              123.45         11.56

    if the 1st character is 3 then

    ID               Code           Num1          Num2          Num3       Num4

    34.52         3.56

    Finally, combine the them for the result

    ID               Code           Num1          Num2          Num3       Num4

    2ABF          CD              123.45         11.56             34.52        3.56

    Please advise if there is a way to do this.

    Thanks so much,

    Ddee

  • Ken McKelvey

    SSCoach

    Points: 18329

    You question does not make sense.

    If you would care to post a sensible question (with consumable test data!!!!!) someone might give you a sensible answer.

    -- *** Consumable test data ***
    CREATE TABLE #MyTable
    (
    MyColumn varchar(100) NOT NULL
    );
    INSERT INTO #MyTable
    VALUES ('2ABF CD000000000012345000000000001156')
    ,('3000000000003452000000000000356');
    -- *** End Consumable test data ***

    WITH N(N)
    AS
    (
    SELECT 1 UNION ALL SELECT 2
    UNION ALL SELECT 3 UNION ALL SELECT 4
    UNION ALL SELECT 5 UNION ALL SELECT 6
    )
    SELECT
    MAX(
    CASE
    WHEN N.N = 1 AND T.MyColumn LIKE '2%'
    THEN LEFT(T.MyColumn, 4)
    END
    ) AS ID
    ,MAX(
    CASE
    WHEN N.N = 2 AND T.MyColumn LIKE '2%'
    THEN SUBSTRING(T.MyColumn, 8, 2)
    END
    ) AS Code
    ,MAX(
    CASE
    WHEN N.N = 3 AND T.MyColumn LIKE '2%'
    THEN CAST(CAST(SUBSTRING(T.MyColumn, 10, 15) AS int)/100.0 AS decimal(15,2))
    END
    ) AS Num1
    ,MAX(
    CASE
    WHEN N.N = 4 AND T.MyColumn LIKE '2%'
    THEN CAST(CAST(SUBSTRING(T.MyColumn, 26, 15) AS int)/100.0 AS decimal(15,2))
    END
    ) AS Num2
    ,MAX(
    CASE
    WHEN N.N = 5 AND T.MyColumn LIKE '3%'
    THEN CAST(CAST(SUBSTRING(T.MyColumn, 2, 15) AS int)/100.0 AS decimal(15,2))
    END
    ) AS Num3
    ,MAX(
    CASE
    WHEN N.N = 6 AND T.MyColumn LIKE '3%'
    THEN CAST(CAST(SUBSTRING(T.MyColumn, 17, 15) AS int)/100.0 AS decimal(15,2))
    END
    ) AS Num4
    FROM #MyTable T
    CROSS JOIN N;
  • Dee Dee-422077

    SSC Eights!

    Points: 930

    Hi Ken,

    Sorry, I wasn't very clear.. Below is a better test data and what I try to accomplish.  Thanks.

    INSERT INTO MyTable

    VALUES ('2 0A1234 CD00000000614150000000067823000 000000000000000')

    ,('3000000003815100000000000000000000000000000000005411500000000000000000000000000')

    ,('2 0A2345 DE00000000123150000000032478000 000000000000000')

    ,('3000000004639200000000000000000000000000000000003421500000000000000000000000000')

    ,('2 0A1256 FW00000000234190000000032517000 000000000000000')

    ,('2 0A2386 FD00000000212190000000021219000 000000000000000');

    And the result I am trying to accomplish:

    the 1st 2 rows have all data for column 1-6 as it has row start with 2 and 3 below each  other

    The last 2 column only have a line start with "2" ( no row start with "3" below it) so the other column is blank

    Column1       Column2       Column3        Column4           Column6

    0A1234         614.15            678.23             381.51                324.78

    0A2345        123.15            324.78            463.92                 342.15

    0A1256         234.19           325.17

    0A2386       212.19             212.19

    Thanks so much for your help Ken.

    Regards,

    Ddee

  • Ken McKelvey

    SSCoach

    Points: 18329

    >the 1st 2 rows have all data for column 1-6 as it has row start with 2 and 3 below each other

    What do you mean by the first two rows?

    How do you link the rows starting with 3 to the corresponding row starting with 2?

    Remember a relation (table) is not a list. A relation is an unordered set.

  • Dee Dee-422077

    SSC Eights!

    Points: 930

    Hi Ken,

    1. I meant in the result- the first 2 rows have all data fill because it has row start with 3 right below row start with a 2

    Column1       Column2       Column3        Column4           Column6

    0A1234         614.15            678.23             381.51                324.78

    0A2345        123.15            324.78            463.92                 342.15

    0A1256         234.19           325.17

    0A2386       212.19             212.19

     

    2.  How do you link the rows starting with 3 to the corresponding row starting with 2?

    only if row starting with 2 has corresponding  row start with 3 in the next line.

    Please advise if it is possible to do this.

    Thanks,

    Ddee

  • Ken McKelvey

    SSCoach

    Points: 18329

    Umm....

    Let me try again.

    A table is an unordered set so how do you tell what are the first two rows? (Obviously not the order you have listed them in as a table is not a list. ie A table is not like an Excel sheet.)

    In your real table maybe they both have a common column which have the same value? Maybe there is a DateAdded or ID column so the order can be determined from that.  If so post new CREATE TABLE and INSERT VALUES statements.

    • This reply was modified 4 weeks, 1 day ago by  Ken McKelvey.
    • This reply was modified 4 weeks, 1 day ago by  Ken McKelvey.
  • Dee Dee-422077

    SSC Eights!

    Points: 930

    Hi Ken,

    There is no relationship between line begin with a 1 and line begin with a 2 except they are next to each other.

    So my guess is this cannot be done.  Please confirm.

    One quick question... is there a way to read those data in sequential order?

    Thanks for your help.

    Ddee

     

  • Ken McKelvey

    SSCoach

    Points: 18329

    Unfortunately in terms of relational database theory the rows are not next to each other as rows are unordered.

    If the source of the rows is something like a CSV file or Excel sheet then you could add a line number column before importing into SQL Server.

    If you only have the table then due to the way SQL server happens to be structured internally you may be able to get away with the following. (ps There are no guarantees this will work especially if the table is large.)

    With your latest test data:

    -- *** Consumable test data ***
    CREATE TABLE #MyTable
    (
    MyColumn varchar(100) NOT NULL
    );
    INSERT INTO #MyTable
    VALUES ('2 0A1234 CD00000000614150000000067823000 000000000000000')
    ,('3000000003815100000000000000000000000000000000005411500000000000000000000000000')
    ,('2 0A2345 DE00000000123150000000032478000 000000000000000')
    ,('3000000004639200000000000000000000000000000000003421500000000000000000000000000')
    ,('2 0A1256 FW00000000234190000000032517000 000000000000000')
    ,('2 0A2386 FD00000000212190000000021219000 000000000000000');
    -- *** End Consumable test data ***

    Add an identity column:

    ALTER TABLE #MyTable
    ADD LineNumber int IDENTITY NOT NULL;

    Now check the following is in the order you want:

    select *
    from #MyTable
    order by LineNumber;

    If the order is correct you can then do the following:

    WITH NextColumns
    AS
    (
    SELECT MyColumn
    ,COALESCE(LEAD(MyColumn) OVER (ORDER BY LineNumber), '') AS MyColumnNext
    FROM #MyTable
    )
    SELECT SUBSTRING(MyColumn, 3, 6) AS ID
    ,SUBSTRING(MyColumn, 10, 2) AS Code
    ,CAST(CAST(SUBSTRING(MyColumn, 12, 13) AS int)/100.0 AS decimal(13,2)) AS Num1
    ,CAST(CAST(SUBSTRING(MyColumn, 25, 13) AS int)/100.0 AS decimal(13,2)) AS Num2
    ,CASE
    WHEN MyColumnNext LIKE '3%'
    THEN CAST(CAST(SUBSTRING(MyColumnNext, 2, 13) AS int)/100.0 AS decimal(13,2))
    END AS Num3
    ,CASE
    WHEN MyColumnNext LIKE '3%'
    THEN CAST(CAST(SUBSTRING(MyColumnNext, 16, 38) AS int)/100.0 AS decimal(38,2))
    END AS Num4
    ,*
    FROM NextColumns
    WHERE MyColumn LIKE '2%';

     

     

  • Dee Dee-422077

    SSC Eights!

    Points: 930

    That works!  Thanks so much for your help, Ken.

    Regards,

    Ddee

  • Ken McKelvey

    SSCoach

    Points: 18329

    If you have imported the LineNumber into SQL Server then everything should be fine.

    If you had to resort to creating the identity column you should, as far as possible, verify the order and document any areas of uncertainty. Just because the result superficially looks fine does not necessarily mean that it is. Areas of uncertainty should also be communicated to the appropriate areas of your organization.

    While you sometimes have to cope with what is dumped on you, in future you should try to avoid bad design.

  • Dee Dee-422077

    SSC Eights!

    Points: 930

    Hi Ken,

    May I ask, when I import the txt file to SQL using SSIS, if I add a column Linenumber of type identity and let it fill in when importing to SQL table.

    Does this the way you said it should be okay?

    Thanks,

    Ddee

  • Ken McKelvey

    SSCoach

    Points: 18329

    I am not sure how SSIS would cope with LineNumber of type identity. Maybe someone with more experience of SSIS can comment.

    If you have the data in a text file I would be inclined to add a LineNumber column directly to the text file and then import it. If you do not want to write a script/program to do this it can be done manually in Excel.

     

Viewing 12 posts - 1 through 12 (of 12 total)

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