I am already using SQL 2016 Standard. Still familiarizing with the features. And the data I received was from various web scrapes through a 3rd party vendor which is why...

I wrote this query but it is not returning entire results. It is failing when TYPE is not an array e.g. Fishing in this instance as it is not JSON...

I also don't like to use Partitions and Ranks etc due to the sorting overhead

The default value for the new column will be set to 1 thus will allow...

You have not coded for the brackets to be displayed. Brackets has to be part of the Expression.

I managed to write the code. I have 4 outputs from the script component: ValidRows, HeaderRows, FooterRows and ErrorRows

The code is as follows:

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

...

I want to store complex transformation business rules within a rules repository table and then use SSIS to call those transformation rules. Not sure how this can be achieved by...

If I correctly understand your requirement, you need to do Pivoting.

Here is the code:

SELECT

PVT.AU,

PVT.DIVISION,

PVT.LOWER_OFFICE,

PVT.[STAND CHART],

PVT.[US BANK]

FROM

(select AU, DIVISION, HIGH_OFFICE, LOWER_OFFICE, GL, GL1, GL + GL1 AS TotalRevenue

from TESTGROUNDFORIDEAS

) SRC

PIVOT

(

SUM(TotalRevenue)

FOR HIGH_OFFICE...

All i wanted was a solution which was provided to me. And thanks for your time to provide SQL Coding Standards and more insight into T-SQL. I am sure lots...

Thanks, This is exactly what I was looking for.

The above code that I provided was just a sample code. In a real Prod environment, there will be a proper table with keys and indexes defined.

Also most of the...

Recursive CTE's are not an issue. It works fine and gives expected result. But yes going back to previous version of SQL it will not work.

Definitely not interested in Cursor...

Need to be done via t-sql

Finally managed to resolve this. The full solution is:

--------------------------------------

-- CREATE DATABASE

--------------------------------------

CREATE DATABASE TEST

GO

USE [TEST]

GO

--------------------------------------

-- DROP TABLE

--------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Test_Apportion]') AND type in (N'U'))

DROP...

I was expecting the result to be like:

TransactionID LedgerID Period Amount Paid ...

I managed to write a sample code and it's working as expected:

DECLARE @t TABLE

(ReadNum int,

GroupLevel int,

SeqNum int

)

Insert Into @t Values (1001,1,1)

Insert Into @t Values (1002,1,2)

Insert Into @t Values (1003,1,3)

Insert Into...