Archive

Archive for April, 2012

TSQL : Parent-Child view to Flattened view using cursors

I recently came across of this requirement from one of my friends, he wanted the following table data to be viewed in a different format. Take a look at the following table

 

Members
Parent
Bikes
Wheel
Steering
Tires
Parent
Clothing
Shirts
Pants
Trousers
Parent
Fruits
Apple
Mango
Orange

 

The above shown table has only one column named members, the records available resembles a parent child notation. With clear ‘report-style’ formatting, we can get enough information from the same table as shown below,

 

Members
Parent
Bikes
  Wheel
  Steering
  Tires
Parent
Clothing
  Shirts
  Pants
  Trousers
Parent
Fruits
  Apple
  Mango
  Orange

Now this table, more or less looks like expand collapse style Parent –  child information,

Requirement:

1. There are records denoting which record is a parent. Here in this example we have record as ‘parent’, it denotes the immediate result after this record as a parent. For example (Bikes, Clothing and Fruits)

 

2. The record which follows ‘parent’ value is the parent like said before, and the successive records till the next ‘parent’ value are its Childs. For example, if ‘Bikes’ is parent then ‘Wheel’, ‘Steering’,’Tires’ are its child values

 

3. This process should be continued till the end of the table and the result should be in the following format,

Parent Child
Bikes Wheel
Bikes Steering
Bikes Tires
Clothing Shirts
Clothing Pants
Clothing Trousers
Fruits Apple
Fruits Mango
Fruits Orange

this is the flattened view of the same parent-child table.

Initially I was trying to bring this result set using SQL query but i ended with cursors. The following snippet is the cursor which brings the required result (flattened view).

Query:

DECLARE @table TABLE
(
members NVARCHAR (max)
)

INSERT INTO @table
SELECT 'Parent'
UNION ALL
SELECT 'Bikes'
UNION ALL
SELECT 'Wheel'
UNION ALL
SELECT 'Steering'
UNION ALL
SELECT 'Tires'
UNION ALL
SELECT 'Parent'
UNION ALL
SELECT 'Clothing'
UNION ALL
SELECT 'Shirts'
UNION ALL
SELECT 'Pants'
UNION ALL
SELECT 'Trousers'
UNION ALL
SELECT 'Parent'
UNION ALL
SELECT 'Fruits'
UNION ALL
SELECT 'Apple'
UNION ALL
SELECT 'Mango'
UNION ALL
SELECT 'Orange'

DECLARE @temp1 VARCHAR (max)
DECLARE @temp2 VARCHAR (max)
DECLARE @Check VARCHAR (max)
DECLARE @Required TABLE
(
parent VARCHAR (max),
child  VARCHAR(max)
)
DECLARE pc_cursor CURSOR FOR
SELECT * FROM   @table

OPEN pc_cursor

FETCH FROM pc_cursor INTO @check

WHILE @@FETCH_STATUS = 0
BEGIN
IF @Check = 'Parent'
BEGIN
FETCH next FROM pc_cursor INTO @temp1
FETCH next FROM pc_cursor INTO @temp2
WHILE ( @temp2 <> 'Parent' )
AND @@FETCH_STATUS = 0
BEGIN
INSERT INTO @Required
(parent,
child)
SELECT @temp1,
@temp2
FETCH next FROM pc_cursor INTO @temp2
IF @temp2 = 'Parent'
BEGIN
FETCH next FROM pc_cursor INTO @temp1
FETCH next FROM pc_cursor INTO @temp2
END
END
END
FETCH next FROM pc_cursor INTO @check
END

SELECT *
FROM   @Required

Output:

 

parent child
Bikes Wheel
Bikes Steering
Bikes Tires
Clothing Shirts
Clothing Pants
Clothing Trousers
Fruits Apple
Fruits Mango
Fruits Orange

I am again going to try the same using tsql query without cursors, if you want to share your queries for bringing this result set, please do post your query as comments.

Happy Querying 😉

%d bloggers like this: