locked
Need help with querry RRS feed

  • Question

  • Hi All,

    Can please kind gentlemen help with below script. What I need to get is to link data from "MYCOLUMN01" to Code (instead of setting values I have column data ready) and "MYCOLUMN02" to Location (instead of setting values I have column data ready).

    Many thanks,

    Yulia

    ______________________________

    with data as (
        select Code,Location from ( values
            ('L698-W-EA',          NULL)
           ,('L82009-EA',          '1/22/333/4444/55555')
           ,('L80401-A-EA',        '7/88/999/10-10/12')
           ,('CWD2132W-BOX-25PK',  'A-AISLE')
           ,('GM22660003-EA',      'B1K2')
        )
        data(Code,Location)
    )
    ,shredded as
    (
        select Code,Location,t.*
        from data
        cross apply [dbo].[DelimitedSplit8K](data.Location,'/') as t
    )
    select 
        pvt.Code
       ,cast(isnull(pvt.[1],' ') as varchar(8)) as Loc1
       ,cast(isnull(pvt.[2],' ') as varchar(8)) as Loc2
       ,cast(isnull(pvt.[3],' ') as varchar(8)) as Loc3
       ,cast(isnull(pvt.[4],' ') as varchar(8)) as Loc4
       ,cast(isnull(pvt.[5],' ') as varchar(8)) as Loc5 
    from shredded
    pivot (max(Item) for ItemNumber in ([1],[2],[3],[4],[5])) pvt;
    ;
    go

    __________________________________

    Sunday, May 29, 2016 5:03 PM

Answers

  • Hi Yulia89,

    You could replace the query as the following script to get the output.

     ;with shredded as
     (
         select ConstituentID, city, total, RAFFLE_RFMLA, customer_id, t.*
         from [dbo].[TEST_DSP_APPENDED_DATA04] as data
         cross apply [dbo].[DelimitedSplit8K](data.RAFFLE_RFMLA,'/') as t
     )
     select 
         ConstituentID, city, total, RAFFLE_RFMLA, customer_id
        ,isnull(pvt.[1], '') as RAFFLE_RFMLA_1
        ,isnull(pvt.[2], '') as RAFFLE_RFMLA_2
        ,isnull(pvt.[3], '') as RAFFLE_RFMLA_3
        ,isnull(pvt.[4], '') as RAFFLE_RFMLA_4
        ,isnull(pvt.[5], '') as RAFFLE_RFMLA_5 
     from shredded
     pivot (max(Item) for ItemNumber in ([1],[2],[3],[4],[5])) pvt;

    Sam Zha
    TechNet Community Support

    • Proposed as answer by tlkena Monday, May 30, 2016 1:20 PM
    • Marked as answer by Naomi N Monday, May 30, 2016 4:50 PM
    Monday, May 30, 2016 5:18 AM

All replies

  • Hello,

    Your post is not clear for me. What/Where are MYCOLUMN01/MYCOLUMN02 from, I can't see it from your SQL code? And what is DelimitedSplit8K?

    Please post DDL, some sample data and the expected result.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Sunday, May 29, 2016 5:46 PM
  •  Dear Olaf,

    Many thanks for response MYCOLUMN01 and  MYCOLUMN02 - are columns in table. In query above they set values manually, but I just want instead of set values manually take values from columns.

    [dbo].[DelimitedSplit8K] - this is the split function.


    Originally what I am trying to do is split data: I have column in table (name is: RAFFLE_RFMLA), column has delimiter and data in this column looks like this: 5/4-5/£10.00-£11.00/78/90+

    My delimiter is "/". SO I want to split this row into 5 columns, like this:

    5     4-5    £10.00-£11.00   78    90+

    I tried ParseName & Replace, but it is not working, as there are more then 4 data sets...  It is worthless to use Left or Right, as number of characters vary. I try to achieve it, without creating extra objects in database, but no clue how.

    Any kind very welcome.

    Many thanks,

    Yulia



    • Edited by Yulia89 Sunday, May 29, 2016 6:29 PM
    Sunday, May 29, 2016 6:23 PM
  • --START
    USE [Mishi]
    CREATE TABLE TEST_DSP_APPENDED_DATA04
        (ConstituentID int, city varchar(255), total int, RAFFLE_RFMLA varchar(255), customer_id int);
    --Add values to table
    INSERT INTO TEST_DSP_APPENDED_DATA04
        (ConstituentID, city, total, RAFFLE_RFMLA, customer_n)
    VALUES
        (1, 'London', 1000, '5/4-5/£10.00-£11.00/78/90+', 101),
        (4, 'NewYork', 765, '4/21/£35/11/12+', 65),
        (7, 'Mexico', 34, '1-3/21-25/£30-£40/12/52', 431),
        (15 ,'Lisbon', 850, '1-8/15/12/14/55+', 102)
    --Check (display) data in table
    USE [Mishi]
    SELECT*
    FROM [dbo].[TEST_DSP_APPENDED_DATA04]

    Sunday, May 29, 2016 6:25 PM
  • So, what seems to be the problem with the code from your first message? It works fine for me.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Sunday, May 29, 2016 8:32 PM
  • Hi Yulia89,

    You could replace the query as the following script to get the output.

     ;with shredded as
     (
         select ConstituentID, city, total, RAFFLE_RFMLA, customer_id, t.*
         from [dbo].[TEST_DSP_APPENDED_DATA04] as data
         cross apply [dbo].[DelimitedSplit8K](data.RAFFLE_RFMLA,'/') as t
     )
     select 
         ConstituentID, city, total, RAFFLE_RFMLA, customer_id
        ,isnull(pvt.[1], '') as RAFFLE_RFMLA_1
        ,isnull(pvt.[2], '') as RAFFLE_RFMLA_2
        ,isnull(pvt.[3], '') as RAFFLE_RFMLA_3
        ,isnull(pvt.[4], '') as RAFFLE_RFMLA_4
        ,isnull(pvt.[5], '') as RAFFLE_RFMLA_5 
     from shredded
     pivot (max(Item) for ItemNumber in ([1],[2],[3],[4],[5])) pvt;

    Sam Zha
    TechNet Community Support

    • Proposed as answer by tlkena Monday, May 30, 2016 1:20 PM
    • Marked as answer by Naomi N Monday, May 30, 2016 4:50 PM
    Monday, May 30, 2016 5:18 AM
  • Dear Sam,

    Thank you very much for that it is amazing. It works perfectly for me.

    Yulia

    Monday, May 30, 2016 10:57 AM