Hieararchy Creation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Hieararchy Creation

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2013-06-04 : 14:24:40

Hi can anyone help to create the hiearchy on customer table

Table resultset:


CLASSIFICATION	TRAIN STATION	INDUSTRY KEY	CUSTOMER
RE	Feature	Major	12345
RE	Feature	Comedy	12456
RE	Feature	Independent	25847
RE	TV - Network	Comedy	45878
RE	TV - Network	Pilot	45645
RE	TV - Cable	Drama	78791
IC	TV - Network	Pilot	787894
IC	TV - Cable	Comedy	45411

Desired output required:


Parent	Child
RE	Feature (RE)
Feature (RE)	Major (Feature RE)
Major (Feature RE)	12345
Feature (RE)	Comedy (Feature RE)
Comedy (Feature RE)	12456
Feature (RE)	Independent (Feature RE)
Independent (Feature RE)	25847
RE	TV - Network[RE]
TV - Network(RE)	Comedy (TV - Network RE)
Comedy (TV - Network RE)	45878
TV - Network(RE)	Pilot (TV - Network RE)
Pilot (TV - Network RE)	45645
TV - Cable(RE)	Drama (TV - Cable RE)
Drama (TV - Cable RE)	78791
IC	TV - Network(IC)
TV - Network(IC)	Pilot (TV - Network IC)
Pilot (TV - Network IC)	787894
IC	TV - Cable(IC)
TV - Cable(IC)	Comedy (TV - Cable IC)
Comedy (TV - Cable IC)	45411

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-04 : 21:49:08

Imitating khtan from your previous post, you should be able to do this (assuming you did not make a cut and paste error in your table result set column names)

Use 'UNION' instead of 'UNION ALL' to eliminate duplicates.

[CODE]

select [Classification] as Parent, [Train]+' ( '+ [Classification]+' )' as Child from @MyActualtable
union
select [Train]+' ( '+ [Classification]+' )' as Parent, [Station] + '('+ [Train] + ' ' + [Classification]+')' as Child from @MyActualtable
union
select [Station] + ' ( '+ [Train] + ' ' + [Classification]+' )' as Parent, CAST([Industry Key] as VARCHAR(30)) as Child from @MyActualtable

[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-04 : 23:52:19

quote:
Originally posted by MuMu88

Imitating khtan from your previous post, you should be able to do this (assuming you did not make a cut and paste error in your table result set column names)

Use 'UNION' instead of 'UNION ALL' to eliminate duplicates.

[CODE]

select [Classification] as Parent, [Train]+' ( '+ [Classification]+' )' as Child from @MyActualtable
union
select [Train]+' ( '+ [Classification]+' )' as Parent, [Station] + '('+ [Train] + ' ' + [Classification]+')' as Child from @MyActualtable
union
select [Station] + ' ( '+ [Train] + ' ' + [Classification]+' )' as Parent, CAST([Industry Key] as VARCHAR(30)) as Child from @MyActualtable

[/CODE]

the way its written currently and seeing the sample data i dont think union makes sense here as the expression seems to be different in each of selects for column

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-05 : 13:21:18

quote:
Originally posted by visakh16

the way its written currently and seeing the sample data i dont think union makes sense here as the expression seems to be different in each of selects for column

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

You are wrong. The union DOES make sense here. See this MSDN page for requirements on UNION: http://msdn.microsoft.com/en-us/library/ms180026.aspx

If you are not convinced, copy this sample data and the query I posted earlier into an SSMS window and Execute.

If you still have questions regarding UNION, or need clarification on the logic implemented in the query, please reply :-)

[CODE]
DECLARE @MyActualTable TABLE ([CLASSIFICATION] VARCHAR(2), [TRAIN] VARCHAR(30), [STATION] VARCHAR(20), [INDUSTRY KEY] INT, [CUSTOMER] VARCHAR(20));
INSERT INTO @MyActualTable ([CLASSIFICATION],[TRAIN], [STATION], [INDUSTRY KEY]) VALUES
('RE', 'Feature', 'Major', 12345);
INSERT INTO @MyActualTable ([CLASSIFICATION],[TRAIN], [STATION], [INDUSTRY KEY]) VALUES ('RE', 'Feature', 'Comedy', 12456);
INSERT INTO @MyActualTable ([CLASSIFICATION],[TRAIN], [STATION], [INDUSTRY KEY]) VALUES ('RE', 'Feature', 'Independent', 25847);
INSERT INTO @MyActualTable ([CLASSIFICATION],[TRAIN], [STATION], [INDUSTRY KEY]) VALUES ('RE', 'TV - Network', 'Comedy', 45878);
INSERT INTO @MyActualTable ([CLASSIFICATION],[TRAIN], [STATION], [INDUSTRY KEY]) VALUES ('RE', 'TV - Network', 'Pilot', 45645);
INSERT INTO @MyActualTable ([CLASSIFICATION],[TRAIN], [STATION], [INDUSTRY KEY]) VALUES ('RE', 'TV - Cable', 'Drama', 78791);
INSERT INTO @MyActualTable ([CLASSIFICATION],[TRAIN], [STATION], [INDUSTRY KEY]) VALUES ('IC', 'TV - Network', 'Pilot', 787894);
INSERT INTO @MyActualTable ([CLASSIFICATION],[TRAIN], [STATION], [INDUSTRY KEY]) VALUES ('IC', 'TV - Cable', 'Comedy', 45411);

[/CODE]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources