Ordering Varchar Column

• vinu512

  SSCoach

  Points: 15729

  More actions

  January 18, 2013 at 3:25 am

  #267123

  Hi Everyone,

  I want a way to correctly Order a field of Varchar Datatype having slashes :

  Following is the sample data:

  Declare @table table(TreeOrder Varchar(Max))

  Insert Into @table

  Select '1'

  Union ALL

  Select '1/8'

  Union ALL

  Select '1/15'

  Union ALL

  Select '1/8/9'

  Union ALL

  Select '1/8/11'

  Union ALL

  Select '1/8/12'

  Union ALL

  Select '1/8/9/16'

  Union ALL

  Select '2'

  Union ALL

  Select '2/13'

  Union ALL

  Select '3'

  Union ALL

  Select '4'

  Union ALL

  Select '5'

  Union ALL

  Select '6'

  Union ALL

  Select '7'

  Following is the output I am looking for :

  TreeOrder

  1

  1/8

  1/8/9

  1/8/11

  1/8/12

  1/8/9/16

  1/15

  2

  2/13

  3

  4

  5

  6

  7

  Its kinda urgent. Please guys...HELP!!

  Vinu Vijayan

  For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

• Ant-Green

  SSC Guru

  Points: 113507

  More actions

  January 18, 2013 at 3:46 am

  #1578618

  You will need to give it some sort of ordering sequence that is the order you want.

  VARCHAR is ordered left to right so 1/15 and 1/8 are orderd 1, then the /, then by 1or8 as 1 < 8 1 comes first which is why you get 1, 1/15, 1/8 etc.

  You will need a way to split the string into its component parts then pivot it into columns then a dynamic order by on the pivot output.

  The 8K string spliter will do the first part, then you will want to look at dynamic pivots or cross tabs (links to all in my signature).

• vinu512

  SSCoach

  Points: 15729

  More actions

  January 18, 2013 at 4:20 am

  #1578632

  Hi Anthony,

  Thanks for the reply........the data I posted earlier is actually the order in which the tree is supposed to be ordered. I was thinking that I could get my data to look like a Tree structure if I sort the data according to the TreeOrder column. But I am unable to do so.

  Here is how my actual data looks like:

  Declare @table table(SModuleId Int, ModuleId Int, SMName Varchar(100), LinkOrder Int, ParentId int, Level int, Root int, TreeOrder Varchar(Max))

  Insert Into @table

  Select 1,1,'Academic',3,0,0,1,'1'

  Union ALL

  Select 8,1,'Content Management',2,1,1,1,'1/8'

  Union ALL

  Select 15,1,'Content Management For Parent',3,1,1,1,'1/15'

  Union ALL

  Select 9,1,'Content',1,8,2,1,'1/8/9'

  Union ALL

  Select 11,1,'Shared File List',2,8,2,1,'1/8/11'

  Union ALL

  Select 12,1,'Shared File List For Faculty',3,8,2,1,'1/8/12'

  Union ALL

  Select 16,1,'InContent',1,9,3,1,'1/8/9/16'

  Union ALL

  Select 2,1,'Administration',2,0,0,2,'2'

  Union ALL

  Select 13,1,'Alumni List',10,2,1,2,'2/13'

  Union ALL

  Select 3,1,'Information',4,0,0,3,'3'

  Union ALL

  Select 4,1,'Personal Information',5,0,0,4,'4'

  Union ALL

  Select 5,1,'School Setup',1,0,0,5,'5'

  Union ALL

  Select 6,1,'Sign Out',8,0,0,6,'6'

  Union ALL

  Select 7,1,'Welcome Page',7,0,0,7,'7'

  And here is the output that is required:

  11Academic 30011

  81Content Management 21111/8

  91Content18211/8/9

  111Shared File List 28211/8/11

  121Shared File List For Faculty 38211/8/12

  161InContent 19311/8/9/16

  151Content Management For Parent 31111/15

  21Administration 20022

  131Alumni List 102122/13

  31Information 40033

  41Personal Information 50044

  51School Setup 10055

  61Sign Out 80066

  71Welcome Page 70077

  Could you please give me an example of what you were saying on the above data....I would implement it further on my actual data.

  Thank you very much.

  Vinu Vijayan

  For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉