Creating Order Numbering Sequence such as 1.1, 2, 2.1, 2.2 using SQL Server CTE

I am trying to create a numbering sequence (1, 1.1, 1.2, 2, 2.1, 2.2, etc.) using CTE in SQL.  I am having so many issues, please help.  Below is my script:

with rs as(
select id, parentid, id [root], title, projectid,
      1 as lvl, id as sort_key
from folder
where parentid is null

union all

select c.id, c.parentid, p.[root], c.title, c.projectid, p.lvl + 1,
      p.lvl & '.' & p.sort_key as step
from rs  p
inner join folder c on p.id = c.parentid
)
select *
from rs
where projectid = 597
order by [root]
vnewman29Asked:
Who is Participating?
 
Kevin CrossChief Technology OfficerCommented:
Hi.

Instead of :
p.lvl & '.' & p.sort_key as step

Open in new window


I might try something like:
p.sort_key + '.' + convert(varchar(10), row_number() over(partition by p.id order by c.id)) as step

Open in new window


Hope that helps.

Kevin
0
 
vnewman29Author Commented:
That didn't work.  I am getting "Conversion failed when converting the varchar value '.' to data type int.
0
 
deightonprogCommented:
how does that work exactly?

1 is a base, then it will contain  1.1 and 1.2
what determines the numbers you use, for example how do you know it will be 1.1 and 1.2 (which fields determine this)

could you give us your table structure, some sample data and a quick explanation?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
vnewman29Author Commented:
The attached image shows the data.  The root field is the main group (1,2,3, ect.) and the parentid is the level and then the id.

For example the 1st group from the image should show as this:

Distribution/Instruction Memoranda 1.0
*_/_/2010 Execution Instruction 1.1
*_/_/2010 Distribution Email 1.2
*Fannie Mae Approvals 1.3
*12/22/2010 Approval 1.4
Transaction Documents 2.0
Untitled.jpg
0
 
Kevin CrossChief Technology OfficerCommented:
That means your id column is INT.  From your original code, I figured you already had that as VARCHAR.  In order for this to work, you will need to convert the ID column to VARCHAR in the anchor query.

i.e., CONVERT(VARCHAR(10), id) as sort_key
0
 
deightonprogCommented:
where does the '2' of Transaction Documents derive from?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.