November 10, 2015 at 5:02 am
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
November 10, 2015 at 7:34 am
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?
November 10, 2015 at 8:16 am
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
November 10, 2015 at 8:28 am
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.
November 10, 2015 at 8:39 am
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
November 10, 2015 at 8:54 am
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.
November 11, 2015 at 12:12 am
-- 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
November 11, 2015 at 8:35 pm
Can anyone help to my previous post
November 12, 2015 at 10:25 pm
Please help to my post
November 13, 2015 at 9:57 am
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.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply