recursive queries - parent child

Question

Post reply

recursive queries - parent child

priestxandar

Right there with Babe

Points: 724
More actions
April 24, 2014 at 6:11 pm

#286061

Hi everyone i need to make some parent-child transformation.
here is test input data:
CREATE TABLE #TestTable(
[Code7] [varchar](7) NOT NULL,
[Code1] [varchar](1) NOT NULL,
[Code2] [varchar](2) NOT NULL,
[Code4] [varchar](4) NOT NULL,
[Code6] [varchar](6) NOT NULL,
CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
(
[Code7] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT #TestTable ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A01AA01', N'A', N'A0', N'A01A', N'A01AA0')
INSERT #TestTable ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A02AB04', N'A', N'A0', N'A02A', N'A02AB0')
INSERT #TestTable ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A02BA02', N'A', N'A0', N'A02B', N'A02BA0')
INSERT #TestTable ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A02BA03', N'A', N'A0', N'A02B', N'A02BA0')
INSERT #TestTable ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A02BC01', N'A', N'A0', N'A02B', N'A02BC0')
when you select the data from the table you will see columns Code1, Code2, Code4, Code6, Code7, those should be transformed to Child column respectively.
Now one row from the table should be transformed into 5 rows, making the transformation like in the ouput. (from 5 rows in the table i should produce 25 rows)
This is the output i should get:
ParentIDChildParRel
1 A NULL -- Child is A ( this is Code1 Column in the table), and Parent-Child relation is NULL
2 A0 1 -- Child is AO ( this is Code2 Column in the table), and Parent-Child relation is 1 (this is the ParentID)
3 A01A 2 -- Child is A01A (this is Code4 Column in the table), and Parent-Child relation is 2 (this is the ParentID)
4 A01AA0 3 -- Child is A01AA0 ( this is Code6 Column in the table), and Parent-Child relation is 3 (this is the ParentID)
5 A01AA01 4 -- Child is A01AA01 ( this is Code7 Column in the table), and Parent-Child relation is 4 (this is the ParentID)
1 A NULL
2 A0 1
3 A02A 2
4 A02AB0 3
5 A02AB04 4
1 A NULL
2 A0 1
3 A02B 2
4 A02BA0 3
5 A02BA02 4
etc...
I can't find solution about this problem. anyone have experience how to handle this ?
Thank you.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

You need to unpivot your table.

An easy way is to use CROSS APPLY and VALUES clause.

SELECT ParentID

,Child

,NULLIF(ParentID-1, 0) AS ParRel

FROM #TestTable t

CROSS APPLY(VALUES(Code1, 1),

(Code2, 2),

(Code4, 3),

(Code6, 4),

(Code7, 5)) u(Child, ParentID)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

priestxandar Right there with Babe Points: 724 More actions · Answer 2

Thanks for the solution you provided to me, what i have realized is that when you unpivot the table i'm going to have duplicate values i need to omit them, so maybe i can explain better what i'm trying to achieve with another test example:

Test data:

CREATE TABLE #TestTable(

[Code7] [varchar](7) NOT NULL,

[Code1] [varchar](1) NOT NULL,

[Code2] [varchar](2) NOT NULL,

[Code4] [varchar](4) NOT NULL,

[Code6] [varchar](6) NOT NULL,

CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED

(

[Code7] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A01AA01', N'A', N'A0', N'A01A', N'A01AA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A02AB04', N'A', N'A0', N'A02A', N'A02AB0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A02BA02', N'A', N'A0', N'A02B', N'A02BA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A02BA03', N'A', N'A0', N'A02B', N'A02BA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A02BC01', N'A', N'A0', N'A02B', N'A02BC0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A02BC02', N'A', N'A0', N'A02B', N'A02BC0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A02BC03', N'A', N'A0', N'A02B', N'A02BC0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A03AB20', N'A', N'A0', N'A03A', N'A03AB2')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A03BA01', N'A', N'A0', N'A03B', N'A03BA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A03FA01', N'A', N'A0', N'A03F', N'A03FA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A03FA04', N'A', N'A0', N'A03F', N'A03FA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A04AA01', N'A', N'A0', N'A04A', N'A04AA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A04AA02', N'A', N'A0', N'A04A', N'A04AA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A04AA03', N'A', N'A0', N'A04A', N'A04AA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A05AA02', N'A', N'A0', N'A05A', N'A05AA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A05BA06', N'A', N'A0', N'A05B', N'A05BA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A06AB06', N'A', N'A0', N'A06A', N'A06AB0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A06AD13', N'A', N'A0', N'A06A', N'A06AD1')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A07AA02', N'A', N'A0', N'A07A', N'A07AA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A07AX03', N'A', N'A0', N'A07A', N'A07AX0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A07BA01', N'A', N'A0', N'A07B', N'A07BA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A07CA00', N'A', N'A0', N'A07C', N'A07CA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A07EC01', N'A', N'A0', N'A07E', N'A07EC0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A07EC02', N'A', N'A0', N'A07E', N'A07EC0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A09AA02', N'A', N'A0', N'A09A', N'A09AA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A10AB00', N'A', N'A1', N'A10A', N'A10AB0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A10AB01', N'A', N'A1', N'A10A', N'A10AB0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A10AB04', N'A', N'A1', N'A10A', N'A10AB0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A10AB05', N'A', N'A1', N'A10A', N'A10AB0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A10AB06', N'A', N'A1', N'A10A', N'A10AB0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A10AC00', N'A', N'A1', N'A10A', N'A10AC0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A10AC01', N'A', N'A1', N'A10A', N'A10AC0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A10AC02', N'A', N'A1', N'A10A', N'A10AC0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A10AD00', N'A', N'A1', N'A10A', N'A10AD0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A10AD01', N'A', N'A1', N'A10A', N'A10AD0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A10AD04', N'A', N'A1', N'A10A', N'A10AD0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A10AD05', N'A', N'A1', N'A10A', N'A10AD0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A10AE00', N'A', N'A1', N'A10A', N'A10AE0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A10AE04', N'A', N'A1', N'A10A', N'A10AE0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A10AE05', N'A', N'A1', N'A10A', N'A10AE0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A10BA02', N'A', N'A1', N'A10B', N'A10BA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A10BB01', N'A', N'A1', N'A10B', N'A10BB0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A10BB02', N'A', N'A1', N'A10B', N'A10BB0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A10BG02', N'A', N'A1', N'A10B', N'A10BG0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A10BX02', N'A', N'A1', N'A10B', N'A10BX0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A10BXO2', N'A', N'A1', N'A10B', N'A10BXO')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A11CA01', N'A', N'A1', N'A11C', N'A11CA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A11CC02', N'A', N'A1', N'A11C', N'A11CC0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A11CC04', N'A', N'A1', N'A11C', N'A11CC0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A11CC05', N'A', N'A1', N'A11C', N'A11CC0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A11DA01', N'A', N'A1', N'A11D', N'A11DA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A11GA01', N'A', N'A1', N'A11G', N'A11GA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A11HA02', N'A', N'A1', N'A11H', N'A11HA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A12AA04', N'A', N'A1', N'A12A', N'A12AA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A12AA06', N'A', N'A1', N'A12A', N'A12AA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A12BA01', N'A', N'A1', N'A12B', N'A12BA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A12BA30', N'A', N'A1', N'A12B', N'A12BA3')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'A12CA02', N'A', N'A1', N'A12C', N'A12CA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B01AA03', N'B', N'B0', N'B01A', N'B01AA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B01AA07', N'B', N'B0', N'B01A', N'B01AA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B01AA08', N'B', N'B0', N'B01A', N'B01AA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B01AB00', N'B', N'B0', N'B01A', N'B01AB0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B01AB01', N'B', N'B0', N'B01A', N'B01AB0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B01AB05', N'B', N'B0', N'B01A', N'B01AB0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B01AB06', N'B', N'B0', N'B01A', N'B01AB0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B01AC05', N'B', N'B0', N'B01A', N'B01AC0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B01AC06', N'B', N'B0', N'B01A', N'B01AC0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B01AC13', N'B', N'B0', N'B01A', N'B01AC1')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B01AC17', N'B', N'B0', N'B01A', N'B01AC1')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B01AD00', N'B', N'B0', N'B01A', N'B01AD0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B01AD01', N'B', N'B0', N'B01A', N'B01AD0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B01AD02', N'B', N'B0', N'B01A', N'B01AD0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B01AD07', N'B', N'B0', N'B01A', N'B01AD0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B01AD10', N'B', N'B0', N'B01A', N'B01AD1')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B02AB01', N'B', N'B0', N'B02A', N'B02AB0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B02BA01', N'B', N'B0', N'B02B', N'B02BA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B02BD02', N'B', N'B0', N'B02B', N'B02BD0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B02BD04', N'B', N'B0', N'B02B', N'B02BD0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B02BD08', N'B', N'B0', N'B02B', N'B02BD0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B02BX02', N'B', N'B0', N'B02B', N'B02BX0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B03AA02', N'B', N'B0', N'B03A', N'B03AA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B03AA07', N'B', N'B0', N'B03A', N'B03AA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B03AB00', N'B', N'B0', N'B03A', N'B03AB0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B03AB04', N'B', N'B0', N'B03A', N'B03AB0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B03AB05', N'B', N'B0', N'B03A', N'B03AB0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B03AB09', N'B', N'B0', N'B03A', N'B03AB0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B03AC00', N'B', N'B0', N'B03A', N'B03AC0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B03AC02', N'B', N'B0', N'B03A', N'B03AC0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B03BA01', N'B', N'B0', N'B03B', N'B03BA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B03XA01', N'B', N'B0', N'B03X', N'B03XA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B05AA01', N'B', N'B0', N'B05A', N'B05AA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B05AA05', N'B', N'B0', N'B05A', N'B05AA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B05AA06', N'B', N'B0', N'B05A', N'B05AA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B05AX02', N'B', N'B0', N'B05A', N'B05AX0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B05BA01', N'B', N'B0', N'B05B', N'B05BA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B05BA02', N'B', N'B0', N'B05B', N'B05BA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B05BA03', N'B', N'B0', N'B05B', N'B05BA0')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B05BA10', N'B', N'B0', N'B05B', N'B05BA1')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B05BA12', N'B', N'B0', N'B05B', N'B05BA1')

INSERT [dbo].[#TestTable] ([Code7], [Code1], [Code2], [Code4], [Code6]) VALUES (N'B05BB01', N'B', N'B0', N'B05B', N'B05BB0')

GO

This code is just better look on the table structure:

SELECT ROW_NUMBER() OVER(ORDER BY Code7) AS 'Parent',

Code1, Code2, Code4, Code6, Code7 FROM #TestTable

and for the Output result how should look like execute this query: ( i have added Description column this time just for the sake of better explanation, that column is not needed)

SELECT TOP 1 1 AS Parent, 'A' AS Child, NULL AS 'Relation', 'This is Root and will have NULL value' AS 'Description of the relation'

FROM #TestTable

UNION ALL

SELECT TOP 1 2 AS Parent, 'B' AS Child, NULL AS 'Relation', 'This is Root and will have NULL value' AS 'Description of the relation'

FROM dbo.#TestTable

UNION ALL

SELECT TOP 1 3 AS Parent, 'A0' AS Child, 1 AS 'Relation', 'This have value 1 from the Parent Column' AS 'Description of the relation'

FROM #TestTable

UNION ALL

SELECT TOP 1 4 AS Parent, 'A1' AS Child, 1 AS 'Relation' , 'This have value 1 from the Parent Column' AS 'Description of the relation'

FROM #TestTable

UNION ALL

SELECT TOP 1 5 AS Parent, 'B0' AS Child, 2 AS 'Relation', 'This have value 2 from the Parent Column' AS 'Description of the relation'

FROM #TestTable

UNION ALL

SELECT TOP 1 6 AS Parent, 'A01A' AS Child, 3 AS 'Relation' , 'This have value 3 because starts with A0, and in Parent Column that is row number 3' AS 'Description of the relation'

FROM #TestTable

UNION ALL

SELECT TOP 1 7 AS Parent, 'A02A' AS Child, 3 AS 'Relation' , 'This have value 3 because starts with A0, and in Parent Column that is row number 3' AS 'Description of the relation'

FROM #TestTable

UNION ALL

SELECT TOP 1 8 AS Parent, 'A02B' AS Child, 3 AS 'Relation', 'This have value 3 because starts with A0, and in Parent Column that is row number 3' AS 'Description of the relation'

FROM #TestTable

UNION ALL

SELECT TOP 1 9 AS Parent, 'A03A' AS Child, 3 AS 'Relation', 'This have value 3 because starts with A0, and in Parent Column that is row number 3' AS 'Description of the relation'

FROM #TestTable

UNION ALL

SELECT TOP 1 10 AS Parent, 'A03B' AS Child, 3 AS 'Relation', 'This have value 3 because starts with A0, and in Parent Column that is row number 3' AS 'Description of the relation'

FROM #TestTable

UNION ALL

SELECT TOP 1 11 AS Parent, 'A03F' AS Child, 3 AS 'Relation', 'This have value 3 because starts with A0, and in Parent Column that is row number 3' AS 'Description of the relation'

FROM #TestTable

UNION ALL

SELECT TOP 1 12 AS Parent, 'A04A' AS Child, 3 AS 'Relation', 'This have value 3 because starts with A0, and in Parent Column that is row number 3' AS 'Description of the relation'

FROM #TestTable

UNION ALL

SELECT TOP 1 13 AS Parent, 'A01AA0' AS Child, 6 AS 'Relation', 'This have value 6 because starts with A01A, and in Parent Column that is row number 6' AS 'Description of the relation'

FROM #TestTable

UNION ALL

SELECT TOP 1 14 AS Parent, 'A02AB0' AS Child, 7 AS 'Relation', 'This have value 7 because starts with A02A, and in Parent Column that is row number 7' AS 'Description of the relation'

FROM #TestTable

UNION ALL

SELECT TOP 1 15 AS Parent, 'A02BA0' AS Child, 8 AS 'Relation', 'This have value 8 because starts with A02B, and in Parent Column that is row number 8' AS 'Description of the relation'

FROM #TestTable

UNION ALL

SELECT TOP 1 16 AS Parent, 'A02BC0' AS Child, 8 AS 'Relation', 'This have value 8 because starts with A02B, and in Parent Column that is row number 8' AS 'Description of the relation'

FROM #TestTable

UNION ALL

SELECT TOP 1 17 AS Parent, 'A03AB2' AS Child, 9 AS 'Relation', 'This have value 9 because starts with A03A, and in Parent Column that is row number 9' AS 'Description of the relation'

FROM #TestTable

UNION ALL

SELECT TOP 1 18 AS Parent, 'A03BA0' AS Child, 10 AS 'Relation', 'This have value 10 because starts with A03B, and in Parent Column that is row number 10' AS 'Description of the relation'

FROM #TestTable

UNION ALL

SELECT TOP 1 19 AS Parent, 'A03FA0' AS Child, 11 AS 'Relation', 'This have value 11 because starts with A03F, and in Parent Column that is row number 11' AS 'Description of the relation'

FROM #TestTable

UNION ALL

SELECT TOP 1 20 AS Parent, 'A04AA0' AS Child, 12 AS 'Relation', 'This have value 12 because starts with A04A, and in Parent Column that is row number 12' AS 'Description of the relation'

FROM #TestTable

UNION ALL

SELECT TOP 1 21 AS Parent, 'A01AA01' AS Child, 13 AS 'Relation', 'This have value 13 because starts with A01AA0, and in Parent Column that is row number 13' AS 'Description of the relation'

FROM #TestTable

UNION ALL

SELECT TOP 1 22 AS Parent, 'A02AB04' AS Child, 14 AS 'Relation', 'This have value 14 because starts with A02AB0, and in Parent Column that is row number 14' AS 'Description of the relation'

FROM #TestTable

UNION ALL

SELECT TOP 1 23 AS Parent, 'A02BA02' AS Child, 15 AS 'Relation', 'This have value 15 because starts with A02BA0, and in Parent Column that is row number 15' AS 'Description of the relation'

FROM #TestTable

UNION ALL

SELECT TOP 1 24 AS Parent, 'A02BA03' AS Child, 15 AS 'Relation', 'This have value 15 because starts with A02BA0, and in Parent Column that is row number 15' AS 'Description of the relation'

FROM #TestTable

UNION ALL

SELECT TOP 1 25 AS Parent, 'A02BC01' AS Child, 16 AS 'Relation', 'This have value 16 because starts with A02BC0, and in Parent Column that is row number 16' AS 'Description of the relation'

FROM #TestTable

UNION ALL

SELECT TOP 1 26 AS Parent, 'A02BC02' AS Child, 16 AS 'Relation', 'This have value 16 because starts with A02BC0, and in Parent Column that is row number 16' AS 'Description of the relation'

FROM #TestTable

UNION ALL

SELECT TOP 1 27 AS Parent, 'A02BC03' AS Child, 16 AS 'Relation', 'This have value 16 because starts with A02BC0, and in Parent Column that is row number 16' AS 'Description of the relation'

FROM #TestTable

UNION ALL

SELECT TOP 1 28 AS Parent, 'A03AB20' AS Child, 17 AS 'Relation', 'This have value 17 because starts with A03AB2, and in Parent Column that is row number 17' AS 'Description of the relation'

FROM #TestTable

UNION ALL

SELECT TOP 1 29 AS Parent, 'A03BA01' AS Child, 18 AS 'Relation', 'This have value 18 because starts with A03BA0, and in Parent Column that is row number 18' AS 'Description of the relation'

FROM #TestTable

UNION ALL

SELECT TOP 1 30 AS Parent, 'A03FA01' AS Child, 19 AS 'Relation', 'This have value 19 because starts with A03FA0, and in Parent Column that is row number 19' AS 'Description of the relation'

FROM #TestTable

UNION ALL

SELECT TOP 1 31 AS Parent, 'A03FA04' AS Child, 19 AS 'Relation', 'This have value 19 because starts with A03FA0, and in Parent Column that is row number 19' AS 'Description of the relation'

FROM #TestTable

UNION ALL

SELECT TOP 1 32 AS Parent, 'A04AA01' AS Child, 20 AS 'Relation', 'This have value 20 because starts with A04AA0, and in Parent Column that is row number 20' AS 'Description of the relation'

FROM #TestTable

UNION ALL

SELECT TOP 1 33 AS Parent, 'B01A' AS Child, 5 AS 'Relation', 'This have value 5 because starts with B0, and in Parent column that is row number 5' AS 'Description of the relation'

FROM #TestTable

UNION ALL

SELECT TOP 1 34 AS Parent, 'B01AA0' AS Child, 33 AS 'Relation', 'This have value 33 because starts with B01A, and in Parent column that is row number 33' AS 'Description of the relation'

FROM #TestTable

Output probably will not look like exactly like i've provided because i haven't done all rows and maybe ordering will be different, but should give you clear idea how parent - child transformation should be done.

Important part is that duplicate values need to be omitted.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

If it were me, I'd break the Parent and Child values up as true parent and child values and set them into an Adjacency list, something like this but missing the positional notation to replace item child ID's...

SELECT DISTINCT

ca.Parent, ca.Child

INTO #Hierarchy

FROM #TestTable

CROSS APPLY

(

SELECT SUBSTRING(Code7,5,2),SUBSTRING(Code7,7,1) UNION ALL

SELECT SUBSTRING(Code7,3,2),SUBSTRING(Code7,5,2) UNION ALL

SELECT SUBSTRING(Code7,2,1),SUBSTRING(Code7,3,2) UNION ALL

SELECT SUBSTRING(Code7,1,1),SUBSTRING(Code7,2,1) UNION ALL

SELECT 'Root' ,SUBSTRING(Code7,1,1) UNION ALL

SELECT NULL ,'Root'

) ca (Parent,Child)

;

SELECT * FROM #Hierarchy

;

Once you figure out the child IDs (missing from above), then you could probably determine parent IDs to have a true and unduplicated adjacency list. Then you could do some rather remarkable things as depicted in the following articles...

http://www.sqlservercentral.com/articles/Hierarchy/94040/

http://www.sqlservercentral.com/articles/T-SQL/94570/

If you're interested in doing such things, then let me know and I'll try to science out a method to make the unique child IDs and associate them with some parent IDs.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

priestxandar Right there with Babe Points: 724 More actions · Answer 4

Hi Jeff,

Thanks for trying to help me, i read those articles you send me, but i cant figure out how to use the info inside to help my self. Dealing with hierarchy tree relations looks hard and confusing.

Also i've tried the sample you provided but its very different from output result i need.

any other suggestion ?

btw thanks for everyone here who are trying to help on Juniors to learn and progress in their career, I appreciate that a lot.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

priestxandar (4/27/2014)
Hi Jeff,
Thanks for trying to help me, i read those articles you send me, but i cant figure out how to use the info inside to help my self. Dealing with hierarchy tree relations looks hard and confusing.
Also i've tried the sample you provided but its very different from output result i need.
any other suggestion ?
btw thanks for everyone here who are trying to help on Juniors to learn and progress in their career, I appreciate that a lot.

No problem. Dealing with hierarchies (in this case, trees with no duplicate nodes and each node has one and only one parent) isn't that difficult but they're bloody impossible when you don't know how. It does take a bit to get the "feel" for them. 🙂 Even when you get the "feel" for them, the lingo can really throw ya because so many people call things by the wrong names. :hehe:

The test data actually does all of the hard work for us. The parent code for every code is already known and available on each line of the test data. All we have to do is "unpivot" that data, de-duplicate it, and voilà, we have our parent/child relationships according to the alpha-numeric codes.

After that, all we have to do is assign a unique numeric value (you called it "Parent") to each Child code and then figure out what the numeric parent (you called it "Relationship") is for each child. Because we already know the parent code for each child, a self-join makes it almost trivial to determine what the numeric parent is for each child except for the very top level. The outer join takes care of that nicely, though.

One other thing. In a correctly formed "Adjacency List" (a fancy name for a Parent/Child table where the Parent ID is listed adjacent to the Child ID), numeric order doesn't matter, especially the somewhat weird order you wanted. That, notwithstanding, I kept the order you requested but that's why the ROW_NUMBER() formula looks a bit complicated.

With all of that in mind, here's a solution for the problem using the great test data you were kind enough to post.

WITH cteNormalize AS

( --=== Normalize the data and split out the parent for each child

SELECT Parent = ROW_NUMBER() OVER (ORDER BY CASE

WHEN LEN(Child) <=2 THEN LEN(Child)

ELSE ASCII(SUBSTRING(Child,1,1))

END

,LEN(Child), Child)

,ca.Child

,ca.ParentCode

FROM #TestTable

CROSS APPLY

( --=== Returns each child and the parent code for each child.

SELECT Code7,Code6 UNION ALL

SELECT Code6,Code4 UNION ALL

SELECT Code4,Code2 UNION ALL

SELECT Code2,Code1 UNION ALL

SELECT Code1,NULL

) ca (Child,ParentCode)

GROUP BY ca.Child,ParentCode

) --=== Determine all of the relationships and display the data in the desired order.

SELECT h2.Parent, h2.Child, RelationShip = h1.Parent

FROM cteNormalize h1

RIGHT JOIN cteNormalize h2

ON h2.ParentCode = h1.Child

ORDER BY h2.Parent

;

A couple of additional notes...

1. Self-joined CTEs execute once for each reference just like Views do. If you can tolerate it, it would be better to put the results of the CTE in a Temp Table and then do a self-join on the Temp Table, instead.

2. This code does exactly what I previously suggested. It creates a unique ChildID (what you're calling Parent) and it creates a ParentID (what you're calling Relationship). If you were to change your column names to what I suggested, and then look back at the code in the articles I provided the links for, you find that if you change EmployeeID to ChildID and ManagerID to ParentID in the code in those articles that the crux of all the code in both articles will work perfectly with your new "Adjacency List", which is what the output of the code above actually is.

Let me know if you have any other questions on this.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

priestxandar Right there with Babe Points: 724 More actions · Answer 6

Jeff Moden (4/27/2014)
priestxandar (4/27/2014)
Hi Jeff,
Thanks for trying to help me, i read those articles you send me, but i cant figure out how to use the info inside to help my self. Dealing with hierarchy tree relations looks hard and confusing.
Also i've tried the sample you provided but its very different from output result i need.
any other suggestion ?
btw thanks for everyone here who are trying to help on Juniors to learn and progress in their career, I appreciate that a lot.
No problem. Dealing with hierarchies (in this case, trees with no duplicate nodes and each node has one and only one parent) isn't that difficult but they're bloody impossible when you don't know how. It does take a bit to get the "feel" for them. 🙂 Even when you get the "feel" for them, the lingo can really throw ya because so many people call things by the wrong names. :hehe:
The test data actually does all of the hard work for us. The parent code for every code is already known and available on each line of the test data. All we have to do is "unpivot" that data, de-duplicate it, and voilà, we have our parent/child relationships according to the alpha-numeric codes.
After that, all we have to do is assign a unique numeric value (you called it "Parent") to each Child code and then figure out what the numeric parent (you called it "Relationship") is for each child. Because we already know the parent code for each child, a self-join makes it almost trivial to determine what the numeric parent is for each child except for the very top level. The outer join takes care of that nicely, though.
One other thing. In a correctly formed "Adjacency List" (a fancy name for a Parent/Child table where the Parent ID is listed adjacent to the Child ID), numeric order doesn't matter, especially the somewhat weird order you wanted. That, notwithstanding, I kept the order you requested but that's why the ROW_NUMBER() formula looks a bit complicated.
With all of that in mind, here's a solution for the problem using the great test data you were kind enough to post.
WITH cteNormalize AS
( --=== Normalize the data and split out the parent for each child
SELECT Parent = ROW_NUMBER() OVER (ORDER BY CASE
WHEN LEN(Child) <=2 THEN LEN(Child)
ELSE ASCII(SUBSTRING(Child,1,1))
END
,LEN(Child), Child)
,ca.Child
,ca.ParentCode
FROM #TestTable
CROSS APPLY
( --=== Returns each child and the parent code for each child.
SELECT Code7,Code6 UNION ALL
SELECT Code6,Code4 UNION ALL
SELECT Code4,Code2 UNION ALL
SELECT Code2,Code1 UNION ALL
SELECT Code1,NULL
) ca (Child,ParentCode)
GROUP BY ca.Child,ParentCode
) --=== Determine all of the relationships and display the data in the desired order.
SELECT h2.Parent, h2.Child, RelationShip = h1.Parent
FROM cteNormalize h1
RIGHT JOIN cteNormalize h2
ON h2.ParentCode = h1.Child
ORDER BY h2.Parent
;
A couple of additional notes...
1. Self-joined CTEs execute once for each reference just like Views do. If you can tolerate it, it would be better to put the results of the CTE in a Temp Table and then do a self-join on the Temp Table, instead.
2. This code does exactly what I previously suggested. It creates a unique ChildID (what you're calling Parent) and it creates a ParentID (what you're calling Relationship). If you were to change your column names to what I suggested, and then look back at the code in the articles I provided the links for, you find that if you change EmployeeID to ChildID and ManagerID to ParentID in the code in those articles that the crux of all the code in both articles will work perfectly with your new "Adjacency List", which is what the output of the code above actually is.
Let me know if you have any other questions on this.

Thanks again for the time spent to explain this issue, explanation is just perfect, when you deal for first time with trees everything looks messy and you dont know where to start, what to do 🙂 but you got my point and did excellent job putting all together.

About row ordering i've done in that way because it was easy for me to keep track what i'm writing, doesn't have to be to that particular order , so row_number() function could be in simple format just with order by clause.

Also the additional comments in the code are very helpful, makes to understand every piece of the code what is doing, in that way we learn better and easier.

When i'm reading the articles again they make more sense now. Thank you again!