Help Needed in SQL to change the data presentation

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

SQL Server 2005 Forums

Transact-SQL (2005)

Help Needed in SQL to change the data presentation

Author

Topic

koushik
Starting Member

11 Posts

Posted - 2010-10-12 : 02:43:35

Hi,

I have a dataset like below :

Tgt_Tbl_Col | Src_Tbl_Col | Level
----------------|----------------------------------|----------
L3_TAB1.COL1 | L2_TAB1.COL3 | 0
L3_TAB1.COL2 | L2_TAB2.COL2 | 0
L3_TAB1.COL3 | L2_TAB2.COL3 | 0
L3_TAB1.COL4 | L2_TAB2.COL1 | 0
L2_TAB2.COL1 | L1_TAB2.COL1 | 1
L1_TAB2.COL1 | Source_Feed2.DATACOL1 | 2
L2_TAB2.COL3 | L1_TAB3.COL3 | 1
L1_TAB3.COL3 | Source_Feed3.DATACOL3 | 2
L2_TAB2.COL2 | L1_TAB1.COL2 | 1
L2_TAB2.COL2 | L1_TAB2.COL2 | 1
L2_TAB2.COL2 | L1_TAB3.COL2 | 1
L1_TAB3.COL2 | Source_Feed3.DATACOL2 | 2
L1_TAB2.COL2 | Source_Feed2.DATACOL2 | 2
L1_TAB1.COL2 | Source_Feed1.DATACOL2 | 2
L2_TAB1.COL3 | L1_TAB1.COL1 | 1
L2_TAB1.COL3 | L1_TAB3.COL1 | 1
L1_TAB3.COL1 | Source_Feed3.DATACOL1 | 2
L1_TAB1.COL1 | Source_Feed1.DATACOL1 | 2

And I want the output in the format like below :

Fina_Table | level1 | level2 | level3
-----------|------------|------------|-----------
L3_TAB1_01 | L2_TAB1_03 | L1_TAB1_01 | Feed1_01
L3_TAB1_01 | L2_TAB1_03 | L1_TAB3_01 | Feed3_01
L3_TAB1_02 | L2_TAB2_02 | L1_TAB1_02 | Feed1_02
L3_TAB1_02 | L2_TAB2_02 | L1_TAB2_02 | Feed2_02
L3_TAB1_02 | L2_TAB2_02 | L1_TAB3_02 | Feed3_02
L3_TAB1_03 | L2_TAB2_03 | L1_TAB3_03 | Feed3_03
L3_TAB1_04 | L2_TAB2_01 | L1_TAB2_01 | Feed2_01

Can you please help me to get it in the best possible way.

Actually this is a sample data I have prepared. In actual scenario table names might not start or end with any specific key word like L1 or L3 etc. Also the level here is clearly 3 but in actual scenario I will not be aware of the level at the beginning. It can be anything 7,8,12,90 .. anything.

Regards,
Koushik Chandra

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-10-12 : 12:01:39

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources