Post reply

complicated sorting parent and child records

  • 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

  • November 10, 2015 at 7:34 am

    #1838845

    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

  • November 10, 2015 at 8:16 am

    #1838863

    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

    #1838871

    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

  • November 10, 2015 at 8:39 am

    #1838877

    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

    #1838885

    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

  • November 11, 2015 at 12:12 am

    #1839031

    -- 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

    #1839257

    Can anyone help to my previous post

  • November 12, 2015 at 10:25 pm

    #1839526

    Please help to my post

  • November 13, 2015 at 9:57 am

    #1839655

    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

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

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