Query for flattened view

Question

Hello All,

I have the following table,

declare @table table (Name nvarchar(max))
insert into @table
select 'Age' union all
select 'Age1' union all
select '1' union all
select '2' union all
select '3' union all
select 'Age' union all
select 'Age2' union all
select '4' union all
select '5' union all
select '6' union all
select 'Age' union all
select 'Age3' union all
select '7' union all
select '8' union all
select '9' 

select * from @table

Result of this table:

Name
Age
Age1
1
2
3
Age
Age2
4
5
6
Age
Age3
7
8
9

I need a result like shown below,

Parent	child
Age1	    1
Age1	    2
Age1	    3
Age2	    4
Age2	    5
Age2	    6
Age3	    7
Age3	    8
Age3	    9

Needed:

1. The second result set as shown above

2. whenever the record 'age' comes in, the next record is parent (for ex: Age1, Age2 ) and following items till the next 'age' record are child values

Is that possible with t-sql query without using cursors

Thanks in advance

---

Dineshkumar

Answer 1

Try this

;With Cte As
(
Select Row_Number() Over (Order By (Select 1)) RwNum,* From @Table
)
,RecCte As
(
Select *,0 As Lvl From Cte Where RwNum = 1
Union All
Select T1.RwNum,T1.Name,Case When T1.Name = 'Age' Then T2.Lvl+1 Else T2.Lvl End
From Cte T1
Inner Join RecCte T2 On T1.RwNum = T2.RwNum+1
)
Select T1.Name,T2.Name From RecCte  T1
Outer Apply RecCte  T2
Where T1.Lvl = T2.Lvl
And T1.Name <> T2.Name And Left(T1.Name,3)<> Left(T2.Name,3)
And IsNumeric(T2.Name)= 1 And IsNumeric(T1.Name) <> 1
And T1.Name <> 'Age'

---

Please have look on the comment

Answer 2

Good One Santhosh !, the sample data which i have provided is not perfect , it can be alphanumeric. I should have given a good example * Sorry about that

Please see following sample,

declare @table table (Name nvarchar(max))
insert into @table
select 'Age' union all
select 'Age1' union all
select '1a' union all
select '2a' union all
select '3a' union all
select 'Age' union all
select 'Age2' union all
select '4b' union all
select '5h' union all
select '6k' union all
select 'Age' union all
select 'Age3' union all
select '7b' union all
select '8g' union all
select '9n' 

select * from @table

Thanks

---

Dineshkumar

Answer 3

declare @table table (Name nvarchar(max))
insert into @table
select 'Age' union all
select 'Age1' union all
select '1' union all
select '2' union all
select '3' union all
select 'Age' union all
select 'Age2' union all
select '4' union all
select '5' union all
select '6' union all
select 'Age' union all
select 'Age3' union all
select '7' union all
select '8' union all
select '9' 


;with cte
as
(
select * ,row_number() over(order by (select 0)) rn
from @table where  PATINDEX('%[0-9]', name)>0
) select rn,
case when PATINDEX('[A-Z]', name)>0 
then name ELSE (SELECT max(name)
                  FROM cte
                  WHERE rn <= t.rn)
       END AS X
 from cte t

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

Answer 4

;With Cte As
(
Select Row_Number() Over (Order By (Select 1)) RwNum,* From @Table
)
,RecCte As
(
Select *,0 As Lvl From Cte Where RwNum = 1
Union All
Select T1.RwNum,T1.Name,Case When T1.Name = 'Age' Then T2.Lvl+1 Else T2.Lvl End
From Cte T1
Inner Join RecCte T2 On T1.RwNum = T2.RwNum+1
)
Select T1.Name,T2.Name From RecCte  T1
Outer Apply RecCte  T2
Where T1.Lvl = T2.Lvl
And T1.Name <> T2.Name And Left(T1.Name,3)<> Left(T2.Name,3)
And IsNumeric(Left(T2.Name,1))= 1 And IsNumeric(Left(T1.Name,1)) <> 1
And T1.Name <> 'Age'

---

Please have look on the comment

Answer 5

Thanks for the effort, the isnumeric (left(T2.Name,1)) in condition cannot be generic since the column is alphanumeric of varying length..

like it can 8b or b8 or 89b or b45

---

Dineshkumar

Answer 6

Although the example data is entered and returns in a specific order; SQL server cannot return the values in a specific order without the ORDER BY clause.

The results of your table can not explicitly and reliably order the rows to make the CTE operate as desired.  If you have another field that indicates the row, much like SanthoshH's query "Select Row_Number() Over (Order By (Select 1)) RwNum,* From @Table", please use that rather than the Row_Number() function.

-r

Answer 7

    :(

---

Regards, Dineshkumar
Please "Mark as Answer" if my post answers your question or "Vote as Helpful" if it helps you