complicated sorting parent and child records

Question

Post reply

complicated sorting parent and child records

ramrajan

SSCommitted

Points: 1737
More actions
November 10, 2015 at 5:02 am

#312995

CREATE TABLE [dbo].[xyzabc11](
[KeyProductControlDisplay] [int] NOT NULL,
[KeyProductControlDisplayParent] [int] NULL,
[ProductCaption] [nvarchar](85) NULL,
[ComparisonOperationValue] [varchar](255) NULL,
[DefaultSelection] [tinyint] NOT NULL,
[FormOrder] [int] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
GO
INSERT [dbo].[xyzabc11] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (548, 526, N'Debt & Other Financings', N'0', 1, 30)
GO
INSERT [dbo].[xyzabc11] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (549, 548, N'Acquisition Financing', N'256', 1, 10)
GO
INSERT [dbo].[xyzabc11] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (550, 548, N'Debt Financing', N'524288', 1, 20)
GO
INSERT [dbo].[xyzabc11] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (551, 548, N'Recapitalization', N'4096', 1, 30)
GO
INSERT [dbo].[xyzabc11] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (552, 526, N'Other', N'1', 1, 40)
GO
select
KeyProductControlDisplay,
KeyProductControlDisplayParent,
ProductCaption,
FormOrder
from dbo.xyzabc11
-- column KeyProductControlDisplay is Primary Key where as KeyProductControlDisplayParent tells the parent
-- Step 1 : Foreach group of Parent and Child Records I need to compare Child FormOrder column < Parent Form Order Condition
-- Categorise True / False Records
10 < 30 - True
20 < 30 - True
All The above records should be descended and merged with false record set below
-- False
30 < 30 - False
-- The final output should be in Below Order
KeyProductControlDisplay KeyProductControlDisplayParent ProductCaption FormOrder
------------------------ ------------------------------ -----------------------------------------------------
548 526 Debt & Other Financings 30
550 548 Debt Financing 20
549 548 Acquisition Financing 10
551 548 Recapitalization 30
552 526 Other 40

Viewing 10 posts - 1 through 9 (of 9 total)

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

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

Probably something like this?

SELECT c.KeyProductControlDisplay,

c.KeyProductControlDisplayParent,

c.ProductCaption,

c.FormOrder

FROM [dbo].[xyzabc11] c

LEFT

JOIN [dbo].[xyzabc11] p ON c.KeyProductControlDisplayParent = p.KeyProductControlDisplay

ORDER BY ISNULL(p.KeyProductControlDisplay, c.KeyProductControlDisplay),

CASE WHEN p.FormOrder IS NULL THEN -999999

WHEN c.FormOrder < p.FormOrder THEN c.FormOrder * -1

ELSE c.FormOrder END

What will happen when you have several levels?

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

ramrajan SSCommitted Points: 1737 More actions · Answer 2

but why this is not working if we have many values in the table

CREATE TABLE [dbo].[xyzabc11](

[KeyProductControlDisplay] [int] NOT NULL,

[KeyProductControlDisplayParent] [int] NULL,

[FOrmOrder] [nvarchar](85) NULL

) ON [PRIMARY]

GO

INSERT [dbo].[xyzabc11] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (526, NULL, N'All Deal Structures')

GO

INSERT [dbo].[xyzabc11] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (527, 526, N'Equity')

GO

INSERT [dbo].[xyzabc11] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (528, 527, N'Acquisition Financing')

GO

INSERT [dbo].[xyzabc11] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (529, 527, N'Follow-on Offering')

GO

INSERT [dbo].[xyzabc11] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (530, 527, N'Growth Capital')

GO

INSERT [dbo].[xyzabc11] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (531, 527, N'IPO')

GO

INSERT [dbo].[xyzabc11] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (532, 527, N'Private Investment in Public Equity (PIPE)')

GO

INSERT [dbo].[xyzabc11] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (533, 527, N'Private Placement')

GO

INSERT [dbo].[xyzabc11] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (534, 527, N'Recapitalization')

GO

INSERT [dbo].[xyzabc11] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (535, 526, N'M&A')

GO

INSERT [dbo].[xyzabc11] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (536, 535, N'Add-on')

GO

INSERT [dbo].[xyzabc11] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (537, 535, N'Asset Acquisition')

GO

INSERT [dbo].[xyzabc11] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (538, 535, N'Buyout (LBO)')

GO

INSERT [dbo].[xyzabc11] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (539, 535, N'Corporate Acquisition')

GO

INSERT [dbo].[xyzabc11] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (540, 535, N'Corporate Divestiture')

GO

INSERT [dbo].[xyzabc11] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (541, 535, N'Growth Capital')

GO

INSERT [dbo].[xyzabc11] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (542, 535, N'Investor Buyout by Management')

GO

INSERT [dbo].[xyzabc11] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (543, 535, N'Management Buy-in (MBI)')

GO

INSERT [dbo].[xyzabc11] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (544, 535, N'Management Buy-out (MBO)')

GO

INSERT [dbo].[xyzabc11] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (545, 535, N'Merger')

GO

INSERT [dbo].[xyzabc11] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (546, 535, N'Public to Private')

GO

INSERT [dbo].[xyzabc11] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (547, 535, N'Secondary Transaction (PE)')

GO

INSERT [dbo].[xyzabc11] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (548, 526, N'Debt & Other Financings')

GO

INSERT [dbo].[xyzabc11] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (549, 548, N'Acquisition Financing')

GO

INSERT [dbo].[xyzabc11] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (550, 548, N'Debt Financing')

GO

INSERT [dbo].[xyzabc11] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (551, 548, N'Recapitalization')

GO

INSERT [dbo].[xyzabc11] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (552, 526, N'Other')

GO

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Can you define "not working" and post expected results based on the new sample data?

P.S. Your new sample data doesn't have the same DDL as the previous one.

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

ramrajan SSCommitted Points: 1737 More actions · Answer 4

Output of new sample data should be

CREATE TABLE [dbo].[xyzabc12](

[KeyProductControlDisplay] [int] NOT NULL,

[KeyProductControlDisplayParent] [int] NULL,

[FOrmOrder] [nvarchar](85) NULL

) ON [PRIMARY]

GO

INSERT [dbo].[xyzabc12] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (526, NULL, N'All Deal Structures')

GO

INSERT [dbo].[xyzabc12] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (527, 526, N'Equity')

GO

INSERT [dbo].[xyzabc12] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (528, 527, N'Acquisition Financing')

GO

INSERT [dbo].[xyzabc12] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (529, 527, N'Follow-on Offering')

GO

INSERT [dbo].[xyzabc12] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (530, 527, N'Growth Capital')

GO

INSERT [dbo].[xyzabc12] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (531, 527, N'IPO')

GO

INSERT [dbo].[xyzabc12] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (532, 527, N'Private Investment in Public Equity (PIPE)')

GO

INSERT [dbo].[xyzabc12] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (533, 527, N'Private Placement')

GO

INSERT [dbo].[xyzabc12] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (534, 527, N'Recapitalization')

GO

INSERT [dbo].[xyzabc12] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (535, 526, N'M&A')

GO

INSERT [dbo].[xyzabc12] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (536, 535, N'Add-on')

GO

INSERT [dbo].[xyzabc12] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (537, 535, N'Asset Acquisition')

GO

INSERT [dbo].[xyzabc12] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (538, 535, N'Buyout (LBO)')

GO

INSERT [dbo].[xyzabc12] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (539, 535, N'Corporate Acquisition')

GO

INSERT [dbo].[xyzabc12] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (540, 535, N'Corporate Divestiture')

GO

INSERT [dbo].[xyzabc12] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (541, 535, N'Growth Capital')

GO

INSERT [dbo].[xyzabc12] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (542, 535, N'Investor Buyout by Management')

GO

INSERT [dbo].[xyzabc12] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (543, 535, N'Management Buy-in (MBI)')

GO

INSERT [dbo].[xyzabc12] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (544, 535, N'Management Buy-out (MBO)')

GO

INSERT [dbo].[xyzabc12] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (545, 535, N'Merger')

GO

INSERT [dbo].[xyzabc12] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (546, 535, N'Public to Private')

GO

INSERT [dbo].[xyzabc12] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (547, 535, N'Secondary Transaction (PE)')

GO

INSERT [dbo].[xyzabc12] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (548, 526, N'Debt & Other Financings')

GO

INSERT [dbo].[xyzabc12] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (550, 548, N'Debt Financing')

GO

INSERT [dbo].[xyzabc12] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (549, 548, N'Acquisition Financing')

GO

INSERT [dbo].[xyzabc12] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (551, 548, N'Recapitalization')

GO

INSERT [dbo].[xyzabc12] ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [FOrmOrder]) VALUES (552, 526, N'Other')

GO

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

Luis Cazares (11/10/2015)
Can you define "not working" and post expected results based on the new sample data?
P.S. Your new sample data doesn't have the same DDL as the previous one.

If you repeat what you posted, I can do it too.

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

ramrajan SSCommitted Points: 1737 More actions · Answer 6

-- column KeyProductControlDisplay is Primary Key where as KeyProductControlDisplayParent tells the parent

-- Step 1 : Foreach group of Parent and Child Records I need to compare Child FormOrder column < Parent Form Order Condition

-- Categorise True / False Records

10 < 30 - True

20 < 30 - True

All The above records should be descended and merged with false record set below

-- False

30 < 30 - False

Below is the script for Input table the required result set also posted in a output table

CREATE TABLE dbo.Input(

[KeyProductControlDisplay] [int] NOT NULL,

[KeyProductControlDisplayParent] [int] NULL,

[ProductCaption] [nvarchar](85) NULL,

[ComparisonOperationValue] [varchar](255) NULL,

[DefaultSelection] [tinyint] NOT NULL,

[FormOrder] [int] NOT NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (526, NULL, N'All Deal Structures', N'0', 1, 0)

GO

INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (527, 526, N'Equity', N'0', 1, 10)

GO

INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (528, 527, N'Acquisition Financing', N'256', 1, 10)

GO

INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (529, 527, N'Follow-on Offering', N'262144', 1, 20)

GO

INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (530, 527, N'Growth Capital', N'4', 1, 30)

GO

INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (531, 527, N'IPO', N'65536', 1, 40)

GO

INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (532, 527, N'Private Investment in Public Equity (PIPE)', N'1024', 1, 50)

GO

INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (533, 527, N'Private Placement', N'8192', 1, 60)

GO

INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (534, 527, N'Recapitalization', N'4096', 1, 70)

GO

INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (535, 526, N'M&A', N'0', 1, 20)

GO

INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (536, 535, N'Add-on', N'64', 1, 10)

GO

INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (537, 535, N'Asset Acquisition', N'128', 1, 20)

GO

INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (538, 535, N'Buyout (LBO)', N'2', 1, 30)

GO

INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (539, 535, N'Corporate Acquisition', N'16384', 1, 40)

GO

INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (540, 535, N'Corporate Divestiture', N'512', 1, 50)

GO

INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (541, 535, N'Growth Capital', N'4', 1, 60)

GO

INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (542, 535, N'Investor Buyout by Management', N'32768', 1, 70)

GO

INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (543, 535, N'Management Buy-in (MBI)', N'8', 1, 80)

GO

INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (544, 535, N'Management Buy-out (MBO)', N'16', 1, 90)

GO

INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (545, 535, N'Merger', N'131072', 1, 100)

GO

INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (546, 535, N'Public to Private', N'2048', 1, 110)

GO

INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (547, 535, N'Secondary Transaction (PE)', N'32', 1, 120)

GO

INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (548, 526, N'Debt & Other Financings', N'0', 1, 30)

GO

INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (549, 548, N'Acquisition Financing', N'256', 1, 10)

GO

INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (550, 548, N'Debt Financing', N'524288', 1, 20)

GO

INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (551, 548, N'Recapitalization', N'4096', 1, 30)

GO

INSERT dbo.Input ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (552, 526, N'Other', N'1', 1, 40)

GO

----

CREATE TABLE dbo.Output(

[KeyProductControlDisplay] [int] NOT NULL,

[KeyProductControlDisplayParent] [int] NULL,

[ProductCaption] [nvarchar](85) NULL,

[ComparisonOperationValue] [varchar](255) NULL,

[DefaultSelection] [tinyint] NOT NULL,

[FormOrder] [int] NOT NULL

) ON [PRIMARY]

INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (526, NULL, N'All Deal Structures', N'0', 1, 0)

GO

INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (527, 526, N'Equity', N'0', 1, 10)

GO

INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (528, 527, N'Acquisition Financing', N'256', 1, 10)

GO

INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (529, 527, N'Follow-on Offering', N'262144', 1, 20)

GO

INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (530, 527, N'Growth Capital', N'4', 1, 30)

GO

INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (531, 527, N'IPO', N'65536', 1, 40)

GO

INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (532, 527, N'Private Investment in Public Equity (PIPE)', N'1024', 1, 50)

GO

INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (533, 527, N'Private Placement', N'8192', 1, 60)

GO

INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (534, 527, N'Recapitalization', N'4096', 1, 70)

GO

INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (535, 526, N'M&A', N'0', 1, 20)

GO

INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (536, 535, N'Add-on', N'64', 1, 10)

GO

INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (537, 535, N'Asset Acquisition', N'128', 1, 20)

GO

INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (538, 535, N'Buyout (LBO)', N'2', 1, 30)

GO

INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (539, 535, N'Corporate Acquisition', N'16384', 1, 40)

GO

INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (540, 535, N'Corporate Divestiture', N'512', 1, 50)

GO

INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (541, 535, N'Growth Capital', N'4', 1, 60)

GO

INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (542, 535, N'Investor Buyout by Management', N'32768', 1, 70)

GO

INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (543, 535, N'Management Buy-in (MBI)', N'8', 1, 80)

GO

INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (544, 535, N'Management Buy-out (MBO)', N'16', 1, 90)

GO

INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (545, 535, N'Merger', N'131072', 1, 100)

GO

INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (546, 535, N'Public to Private', N'2048', 1, 110)

GO

INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (547, 535, N'Secondary Transaction (PE)', N'32', 1, 120)

GO

INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (548, 526, N'Debt & Other Financings', N'0', 1, 30)

GO

INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (550, 548, N'Debt Financing', N'524288', 1, 20)

GO

INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (549, 548, N'Acquisition Financing', N'256', 1, 10)

GO

INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (551, 548, N'Recapitalization', N'4096', 1, 30)

GO

INSERT dbo.Output ([KeyProductControlDisplay], [KeyProductControlDisplayParent], [ProductCaption], [ComparisonOperationValue], [DefaultSelection], [FormOrder]) VALUES (552, 526, N'Other', N'1', 1, 40)

GO

ramrajan SSCommitted Points: 1737 More actions · Answer 7

ramrajan

SSCommitted

Points: 1737

November 11, 2015 at 8:35 pm

#1839257

Can anyone help to my previous post

ramrajan SSCommitted Points: 1737 More actions · Answer 8

ramrajan

SSCommitted

Points: 1737

November 12, 2015 at 10:25 pm

#1839526

Please help to my post

Luis Cazares SSC Guru Points: 183706 More actions · Answer 9

This might do the trick. I can't understand why would anyone want this order.

WITH rCTE AS(

SELECT [KeyProductControlDisplay],

[KeyProductControlDisplayParent],

[ProductCaption],

[ComparisonOperationValue],

[DefaultSelection],

[FormOrder],

CAST( RIGHT( 10000 + [FormOrder], 4) AS varchar(100)) AS [compositeKey]

FROM Input i

WHERE i.KeyProductControlDisplayParent IS NULL

UNION ALL

SELECT i.[KeyProductControlDisplay],

i.[KeyProductControlDisplayParent],

i.[ProductCaption],

i.[ComparisonOperationValue],

i.[DefaultSelection],

i.[FormOrder],

CAST( r.[compositeKey] + '.'

+ CAST( RIGHT( 10000 + ROW_NUMBER() OVER( ORDER BY CASE WHEN i.FormOrder < r.FormOrder THEN i.FormOrder * -1

ELSE i.FormOrder END ), 4) AS varchar(100)) AS varchar(100))

FROM Input i

JOIN rCTE r ON i.KeyProductControlDisplayParent = r.KeyProductControlDisplay

)

SELECT [KeyProductControlDisplay],

[KeyProductControlDisplayParent],

[ProductCaption],

[ComparisonOperationValue],

[DefaultSelection],

[FormOrder]

FROM rCTE r

ORDER BY [compositeKey]

Be aware that recursive CTEs can be a performance problem depending on indexing and data. If this is a problem, you might want to check the option of a set-based loop[/url] to generate composite key used to order in here.

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