Post reply

query help

  • December 11, 2017 at 11:19 pm

    #325016

    IF OBJECT_ID('Tempdb..#tStudents') IS NOT NULL
    DROP TABLE #tStudents

    CREATE TABLE #tStudents(
      [StudentID] [varchar] (6) NOT NULL ,
      [StudentName] [varchar](50) NOT NULL,
      [Code1] [varchar] (MAX) NULL,
         [Code2] [varchar] (MAX) NULL,
         [Code3] [varchar] (MAX) NULL)
    GO

    INSERT INTO #tStudents VALUES ('SID1','Amar','E1,E2,E3','R1,R2, ','Y1,Y2,Y3')
    INSERT INTO #tStudents VALUES ('SID2','Bobi','X1,X2,X3','D1,D2,D3','K1,K2,K3')
    INSERT INTO #tStudents VALUES ('SID3','Cathy','A1,A2,A3','E1,E2,E3','S1, ,S3')
    GO
    SELECT * FROM    #tStudents

    ;WITH Cte AS
    (
      SELECT
       [StudentID],
       [StudentName],
       CAST('<M>' + REPLACE([Code1], ',' , '</M><M>') + '</M>' AS XML) AS [Code1]
      FROM #tStudents
    )
    Select
      [StudentID],
      [StudentName],
      Split.a.value('.', 'VARCHAR(MAX)') AS [Code1]
    FROM Cte
    CROSS APPLY [Code1].nodes('/M')Split(a)
    --Need to add Code2 and Code3 columns

    Output Needed:

    StudentIDStudentNameCode1Code2Code3
    SID1AmarE1R1Y1
    SID1AmarE2R2Y2
    SID1AmarE3Y3
    SID2BobiX1D1K1
    SID2BobiX2D2K2
    SID2BobiX3D3K3
    SID3CathyA1E1S1
    SID3CathyA2E2
    SID3CathyA3E3S3

  • December 12, 2017 at 1:06 am

    #1971697

    How do you determine which Code1 is matched with which Code2 and which Code3 in your result set?  Will all codes always have the same number of elements in?

    John

  • December 12, 2017 at 2:12 am

    #1971704

    If not the same number, is it the order in which they appear in the delimited list? (which, in your examples is the same, but who knows for your real data :))

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • December 12, 2017 at 5:13 am

    #1971715

    yes and yes for above questions.

  • December 12, 2017 at 5:21 am

    #1971716

    etirem - Tuesday, December 12, 2017 5:13 AM

    yes and yes for above questions.

    It can't be both... If I have the delimited list 'E1, E3, E2', you're stating it must be supplied in the order of the list and in the order of the numeric part. E3 appears in the list prior to E2, but 2 is less than 3.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • December 12, 2017 at 5:49 am

    #1971722

    Thom A - Tuesday, December 12, 2017 5:21 AM

    etirem - Tuesday, December 12, 2017 5:13 AM

    yes and yes for above questions.

    It can't be both... If I have the delimited list 'E1, E3, E2', you're stating it must be supplied in the order of the list and in the order of the numeric part. E3 appears in the list prior to E2, but 2 is less than 3.

    Sorry for the confusion. Only the order of the list and it will be always 3 values for Code cols. 
    For each StudentID, StudentName, there are 3 cols with Code1,Code2 and Code3. The values may differ in these columns but it will be always 2 Commas seperated repectivel. For Ex: 'E1,E2,E3'           'R1,R2, '             'Y1,Y2,Y3'. Here for  'R1,R2, '  it will be R1 ..R2 ..BlankValue

  • December 12, 2017 at 5:59 am

    #1971723

    This is a little bit of a "quick answer", as I'm sure there's faster, however, you could use the DelimitedSplit8K function:
    SELECT t.StudentID,
           t.StudentName,
           C1.Item AS Code1,
           C2.Item As Code2,
           C3.Item AS Code3
    FROM #tStudents t
        CROSS APPLY dbo.DelimitedSplit8K(t.Code1, ',') C1
        CROSS APPLY dbo.DelimitedSplit8K(t.Code2, ',') C2
        CROSS APPLY dbo.DelimitedSplit8K(t.Code3, ',') C3
    WHERE C1.ItemNumber = C2.ItemNumber AND C2.ItemNumber = C3.ItemNumber;

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • December 12, 2017 at 8:59 am

    #1971771

    Thom A - Tuesday, December 12, 2017 5:59 AM

    This is a little bit of a "quick answer", as I'm sure there's faster, however, you could use the DelimitedSplit8K function:
    SELECT t.StudentID,
           t.StudentName,
           C1.Item AS Code1,
           C2.Item As Code2,
           C3.Item AS Code3
    FROM #tStudents t
        CROSS APPLY dbo.DelimitedSplit8K(t.Code1, ',') C1
        CROSS APPLY dbo.DelimitedSplit8K(t.Code2, ',') C2
        CROSS APPLY dbo.DelimitedSplit8K(t.Code3, ',') C3
    WHERE C1.ItemNumber = C2.ItemNumber AND C2.ItemNumber = C3.ItemNumber;

    Helpful...thank you.

  • December 13, 2017 at 6:14 am

    #1971937

    +1 for delimited split 8K
    be aware though that you are doing a 3 way Cartesian join so with 3 elements you are selecting 1 from 27 combinations, with 4 elements you would be selecting 1 from 56, with 10 elements you will be selecting 1 in 1000 so the performance will go down hill quickly.  If you have more than three fields then exponent will increase.  10 elements across 5 columns = 1 in 100000
    HOWEVER:

    If you have the opportunity to refactor your database to provide a proper 3NF form you will benefit greatly 🙂

Viewing 9 posts - 1 through 8 (of 8 total)

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