# 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 Thursday, August 07, 2014 6:27 PM
Thursday, August 07, 2014 6:26 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 Friday, August 08, 2014 1:46 PM
Thursday, August 07, 2014 7:40 PM

### 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
• 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

```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 Friday, August 08, 2014 1:46 PM
Thursday, August 07, 2014 7:40 PM

• 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