Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.

View in another Language

Sponsored [Advertise Here]
Syndicate your blog!
beyondrelational.com
Syndicate your blog with us to get wider reach into the technology community. Click here to get started.

Don't have a blog yet?
beyondrelational.com
Start writing a blog at beyondrelational.com, get recognized and build your brand

Izenda Reports
izenda.com
Ad Hoc reporting for ASP.NET that actually works

TSQL Challenge 60 - Generate a relational tree structure from a string

An ETL application receives product and category information in a text file. The category hierarchy is specified as a string. Your job is to read the hierarchy information and generate a relational table (result set) representing the hierarchy of categories.

Want to make SQL effortless?
With code-completion, SQL reformatting, script summaries and more, SQL Prompt 5 makes writing, editing, and exploring SQL effortless. Download a 14-day free trial now.

Sample Data

Categories Table

Categories
---------------------------
food/biscuits/britania
food/milk
food/baby/icecream
food/baby/milk powder
detergents/liquids/handwash

Expected Results

id  name        parent
--- ----------- -------
1   detergents  null
2   food        null
3   liquids     1
4   baby        2
5   biscuits    2
6   milk        2
7   handwash    3
8   icecream    4
9   milk powder 4
10  britania    5

Rules

  1. Pay attention to the order of the output table. The "ID" values of the categories are generated in a special way. The values are ordered by "parent" and then by "name".
  2. There is no limit in the number of levels in the hierarchy.
  3. Each hierarchy node will have at most one parent.
  4. Each category ends in a terminal node of a hierachy and begins with the main parent node of the hierarchy.
  5. There will be no duplicate entries.

Sample Script

Use the TSQL Script given below to generate the source table and fill them with sample data.

IF OBJECT_ID('TC60','U') IS NOT NULL BEGIN
	DROP TABLE TC60
END
GO

CREATE TABLE TC60(
	Categories VARCHAR(MAX)
)
GO

INSERT INTO TC60(Categories)
SELECT 'food/biscuits/britania' UNION ALL
SELECT 'food/milk' UNION ALL
SELECT 'food/baby/icecream' UNION ALL
SELECT 'food/baby/milk powder' UNION ALL
SELECT 'detergents/liquids/handwash' 

SELECT * FROM TC60

Restrictions

  1. The solution should be a single query that starts with a "SELECT" or “;WITH”

Notes

  1. Read the Submission Guidelines and make sure that your solution follows them.
  2. If you would like to use a Tally Table, you can use the script given here. Your solution should not include the script to create and populate the tally table. You can assume that the tally table will be available in the database where the evaluation team will run your Code.
Connect any source control system to SQL Server
SQL Source Control 2.1 connects all source control systems - TFS, SVN, Vault Professional, Kiln, Mercurial, Git, Perforce, CVS, and Bazaar - to SQL Server.
More information and free trial.

Jacob Sebastian
1 · 100.00% · 32235
Submit a Solution
View Solutions
View All Challenges Previous Challenge Next Challenge
9
Liked
40
Comments
74
Solutions



Submit

40   Comments   

Subscribe to Notifications
  • Can names have trailing spaces?

    INSERT INTO TC60(Categories)
    SELECT 'a/b' UNION ALL
    SELECT 'a/b '
    
    commented on Jul 11 2011 6:38AM  .  Report Abuse This post is not formatted correctly
    Leszek Gniadkowski
    8 · 18% · 5729
  • ¿Does anyone know some trick to generate lots of data without duplicate records to test performance?

    commented on Jul 12 2011 1:41AM  .  Report Abuse This post is not formatted correctly
    diegoperdices
    113 · 1% · 459
  • @Leszek,

    If you find any trailing spaces, you can safely truncate the trailing spaces.

    commented on Jul 12 2011 3:06AM  .  Report Abuse This post is not formatted correctly
    Jacob Sebastian
    1 · 100% · 32235
  • @Jacob

    Thx

    @diegoperdices

    My data, result (first 10 and last 10) and stats:

    INSERT INTO TC60(Categories)
    select 'Computers/Apple/Macintosh' union all
    select 'Food/Apple/Lobo' union all
    select 
        'x' + cast((select cast('/' as varchar(max)) + replicate('a',1 + (tl.N/2 - abs(x.N - tl.N / 2)) * 4) + cast(tl.N + 1 as varchar(max)) + '_'+ cast(x.N + 1 as varchar(max))
        				from tsqlc_Tally x where x.N < tl.N + 2
        				for xml path('') ,type)
        			 as varchar(max))
    from tsqlc_Tally tl where tl.N < 100
    
    
    1   Computers	NULL
    2   Food	NULL
    3   x	NULL
    4   Apple	1
    5   Apple	2
    6   a100_1	3
    7   a10_1	3
    8   a11_1	3
    9   a12_1	3
    10  a13_1	3
    
    
    4998    aaaaa98_96	4896
    4999    aaaaaaaaaaaaa99_96	4897
    5000    aaaaaaaaa100_97	4898
    5001    a97_97	4899
    5002    a98_97	4900
    5003    aaaaaaaaa99_97	4901
    5004    aaaaa100_98	4902
    5005    aaaaa99_98	4903
    5006    a100_99	4904
    5007    a99_99	4905
    
    
    (5007 row(s) affected)
    Table 'Worktable'. Scan count 2, logical reads 30452, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'TC60'. Scan count 1, logical reads 39, physical reads 0, read-ahead reads 0, lob logical reads 192, lob physical reads 0, lob read-ahead reads 144.
    
     SQL Server Execution Times:
       CPU time = 4719 ms,  elapsed time = 5496 ms.
    
    commented on Jul 12 2011 4:07AM  .  Report Abuse This post is not formatted correctly
    Leszek Gniadkowski
    8 · 18% · 5729
  • hmmm - Leszek - - could you or Jacob confirm that the results for these two lines should look as I have them below please ?? Just want to make sure I understand the problem correctly

    Computers/Apple/Macintosh

    Food/Apple/Lobo

    1---Computers---Null

    2---Food---Null

    3---Apple---1

    4---Apple---2

    5---Macintosh---3

    6---Lobo---4

    commented on Jul 12 2011 4:32AM  .  Report Abuse This post is not formatted correctly
    DaveR - UK
    112 · 1% · 463
  • Hello,

    Is it possible to have a category which is parent and terminal node at the same time? In other words will there always be '/' in the path?

    Thanks

    commented on Jul 12 2011 5:17AM  .  Report Abuse This post is not formatted correctly
    lof
    224 · 1% · 201
  • @lof, Only the leaf level nodes can have items within them. So a category can either have other child categories or can be at the leaf level and can have items.

    commented on Jul 12 2011 5:45AM  .  Report Abuse This post is not formatted correctly
    Jacob Sebastian
    1 · 100% · 32235
  • OK, there is no limit in the number of levels in the hierarchy, but are there limit in length of Categories field? Or to put it simple, is Categories varchar(max) or maybe varchar(8000)?

    commented on Jul 12 2011 6:01AM  .  Report Abuse This post is not formatted correctly
    Mario Puskaric
    154 · 1% · 321
  • So: 'Computers/Apple/Macintosh' & 'Food/Apple/Lobo' are:

    1 Computers Null

    2 Food Null

    3 Apple 1

    4 Apple 2 ......

    but 'food/baby/milk powder' & 'food/baby/icecream' are:

    2 food Null

    4 baby 2 ....

    is this correct?

    commented on Jul 12 2011 7:54AM  .  Report Abuse This post is not formatted correctly
    diegoperdices
    113 · 1% · 459
  • @Leszek thanks for test data, but are you sure your sorting is ok?

    Lets take row 6 and 7:

    6   a100_1  3 
    7   a10_1   3 
    

    In my opinion a101 should be before a1001. Below my results and stats:

    1   Computers	NULL
    2   Food	NULL
    3   x	NULL
    4   Apple	1
    5   Apple	2
    6   a1_1	3
    7   a10_1	3
    8   a100_1	3
    9   a11_1	3
    10  a12_1	3
    
    5000    aaaaaaaaaaaaa100_96	4994
    5001    aaaaaaaaaaaaa99_96	4995
    5002    a97_97	4996
    5003    a98_97	4997
    5004    aaaaaaaaa100_97	4998
    5005    aaaaaaaaa99_97	4999
    5006    aaaaa100_98	5002
    5007    aaaaa99_98	5003
    5008    a100_99	5004
    5009    a99_99	5005
    
    (5009 row(s) affected)
    Table 'Worktable'. Scan count 12, logical reads 179040, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'TC60'. Scan count 6, logical reads 234, physical reads 0, read-ahead reads 0, lob logical reads 708, lob physical reads 0, lob read-ahead reads 0.
    
     SQL Server Execution Times:
       CPU time = 8375 ms,  elapsed time = 8552 ms.
    
    commented on Jul 12 2011 8:06AM  .  Report Abuse This post is not formatted correctly
    Maciej Chrzanowski
    92 · 2% · 594
Previous 1 | 2 | 3 | 4 Next

Your Comment


Sign Up or Login to post a comment.


Managed Windows Shared Hosting by OrcsWeb

Copyright © Rivera Informatic Private Ltd.