none
complex query

    Question

  • I am trying to get following result set out of single column. In a column when it hit a particular value in columnA it will change column B value 

    Table A

    Column A

    -----------------

    111

    AAA

    BBB

    CCC

    AAA

    222

    XXX

    AAA

    DDD


    Result

    Column A      COLUMNB

    AAA                111

    BBB                111

    CCC               111

    AAA                111

     

    XXX                 222

    AAA                 222

    DDD                222


    • Edited by SGK1222 Thursday, August 07, 2014 6:27 PM
    Thursday, August 07, 2014 6:26 PM

Answers

  • Create table TableA (id int identity(1,1), ColumnA varchar(10))
    insert into TableA values('ABCD1234'),('AAA'),('BBB'),('CCC'),('XYZA1234'),('XXX'),('AAA'),('DDD')
    
    
    ;with mycte as (
    select ColumnA
    ,Case WHEN ISNUMERIC(Right(ColumnA,4))=1  Then ColumnA 
     Else null 
    END ColumnB  ,Row_number() Over(Order by id) rn   
    FROM TableA )
    
    Select  ColumnA,ColumnB  FROM (Select ColumnA,d.ColumnB from mycte m
    Cross apply (select top 1 ColumnB from mycte m2 
    WHERE m2.rn<=m.rn and ColumnB is not null   
    Order by rn DESC) d(ColumnB) ) t
    WHERE ColumnA<>ColumnB
    
    
    Drop table TableA

    • Marked as answer by SGK1222 Friday, August 08, 2014 1:46 PM
    Thursday, August 07, 2014 7:40 PM
    Moderator

All replies

  • Create table TableA (id int identity(1,1), ColumnA varchar(10))
    insert into TableA values('111'),('AAA'),('BBB'),('CCC'),('222'),('XXX'),('AAA'),('DDD')
    
    
    ;with mycte as (
    select ColumnA
    ,Case WHEN ISNUMERIC(ColumnA)=1  Then ColumnA 
     Else null 
    END ColumnB  ,Row_number() Over(Order by id) rn   
    FROM TableA )
    
    Select ColumnA,d.ColumnB from mycte m
    Cross apply (select top 1 ColumnB from mycte m2 
    WHERE m2.rn<=m.rn and ColumnB is not null   
    Order by rn DESC) d(ColumnB) 
    WHERE  ISNUMERIC(ColumnA)=0
    
    
    Drop table TableA

    Thursday, August 07, 2014 6:40 PM
    Moderator
  • Thanks for the reply but i think i have not put the example correct.

    Column A

    -----------------

    ABCD1234

    AAA

    BBB

    CCC

    AAA

    XYZA1234

    XXX

    AAA

    DDD

    Result

    Column A      COLUMNB

    AAA                ABCD1234

    BBB                ABCD1234

    CCC               ABCD1234

    AAA                ABCD1234

     

    XXX                 XYZA1234

    AAA                 XYZA1234

    DDD                XYZA1234

    Thursday, August 07, 2014 6:47 PM
  • Please try this:

    DECLARE @A TABLE
        (
          id INT PRIMARY KEY
                 IDENTITY ,
          A NVARCHAR(10)
        )
    
    INSERT  @A
            ( A )
    VALUES  ( 'ABCD1234' ),
            ( 'AAA' ),
            ( 'BBB' ),
            ( 'CCC' ),
            ( 'AAA' ),
            ( 'XYZA1234' ),
            ( 'XXX' ),
            ( 'AAA' ),
            ( 'DDD' );
    WITH    c1
              AS ( SELECT   id ,
                            A ,
                            CASE WHEN LEN(a) = 8 THEN 1
                                 ELSE 0
                            END AS new
                   FROM     @A
                 ),
            c2
              AS ( SELECT   a.id Aid ,
                            a.A AA ,
                            a.new Anew ,
                            b.id Bid ,
                            b.A BA ,
                            b.new Bnew ,
                            ROW_NUMBER() OVER ( PARTITION BY a.id ORDER BY b.id DESC ) rn
                   FROM     c1 AS a
                            CROSS JOIN c1 AS b
                   WHERE    a.id > b.id
                            AND CASE WHEN LEN(b.A) = 8 THEN 1
                                     ELSE 0
                                END = 1
                            AND CASE WHEN LEN(a.A) = 8 THEN 1
                                     ELSE 0
                                END = 0
                 )
        SELECT  AA ,
                BA
        FROM    c2
        WHERE   rn = 1


    Saeid Hasani [sqldevelop]

    Thursday, August 07, 2014 7:11 PM
  • Create table TableA (id int identity(1,1), ColumnA varchar(10))
    insert into TableA values('ABCD1234'),('AAA'),('BBB'),('CCC'),('XYZA1234'),('XXX'),('AAA'),('DDD')
    
    
    ;with mycte as (
    select ColumnA
    ,Case WHEN ISNUMERIC(Right(ColumnA,4))=1  Then ColumnA 
     Else null 
    END ColumnB  ,Row_number() Over(Order by id) rn   
    FROM TableA )
    
    Select  ColumnA,ColumnB  FROM (Select ColumnA,d.ColumnB from mycte m
    Cross apply (select top 1 ColumnB from mycte m2 
    WHERE m2.rn<=m.rn and ColumnB is not null   
    Order by rn DESC) d(ColumnB) ) t
    WHERE ColumnA<>ColumnB
    
    
    Drop table TableA

    • Marked as answer by SGK1222 Friday, August 08, 2014 1:46 PM
    Thursday, August 07, 2014 7:40 PM
    Moderator


  • CREATE TABLE #temp (ID INT IDENTITY (1,1),VALUE VARCHAR(10))
    GO

    INSERT INTO #temp  (VALUE)
    VALUES ('111')
    INSERT INTO #temp (VALUE)
    VALUES ('AAA')
    INSERT INTO #temp (VALUE)
    VALUES ('BBB')
    INSERT INTO #temp (VALUE)
    VALUES ('CCC')
    INSERT INTO #temp (VALUE)
    VALUES ('AAA')
    INSERT INTO #temp (VALUE)
    VALUES ('222')
    INSERT INTO #temp (VALUE)
    VALUES ('XXX')
    INSERT INTO #temp (VALUE)
    VALUES ('AAA')
    INSERT INTO #temp (VALUE)
    VALUES ('DDD')
    INSERT INTO #temp (VALUE)
    VALUES ('333')
    INSERT INTO #temp (VALUE)
    VALUES ('XXX')
    INSERT INTO #temp (VALUE)
    VALUES ('AAA')
    INSERT INTO #temp (VALUE)
    VALUES ('DDD')




    SELECT XX.VALUE As ColumnA,X.VALUE ColumnB
    FROM #temp X 
    INNER   JOIN (
    SELECT  VALUE,
    (SELECT MAX (ID) FROM  #temp B WHERE ISNUMERIC(VALUE) = 1 AND B.ID < A.ID ) AS ID
    FROM #temp A
    where ISNUMERIC(VALUE) <> 1 ) XX
    ON X.ID =  XX.ID
    GO


    DROP TABLE #temp
    GO

    MCTS 2008 & 2005 , MCITP 2008 -- Please remember to mark the post as answered if it answers your question.

    Thursday, August 07, 2014 9:47 PM