Hi pratikshir,
Inorder to get the data from the string you need to use while loop with the table where you have specied the column names and the length details and retrive the specific part of string using the SUBSTRING function.
Check the below test query.
SQL
DECLARE @Test AS Table(Id INT IDENTITY,Description VARCHAR(100),Format VARCHAR(50),Length INT,Decimal CHAR(5),Start_Position INT)
INSERT INTO @Test VALUES('Participant ID','Character',3,'--',1)
INSERT INTO @Test VALUES('Transaction Type','Character',2,'--',4)
INSERT INTO @Test VALUES('From Account Type','Character',2, '--' ,6)
INSERT INTO @Test VALUES('To Account Type','Character',2,'--',8)
INSERT INTO @Test VALUES('Transaction Serial','Numeric',12, 0, 10)
INSERT INTO @Test VALUES('Response Code','Character', 2 ,'--', 22)
INSERT INTO @Test VALUES('PAN Number','Character', 19 ,'--', 24)
INSERT INTO @Test VALUES('Member Number','Character', 1 ,'--', 43)
INSERT INTO @Test VALUES('Approval Number','Character', 6 ,'--', 44)
INSERT INTO @Test VALUES('System Trace Audit Number','Numeric', 12 ,0, 50)
INSERT INTO @Test VALUES('Transaction Date','Numeric', 6, 0, 62)
INSERT INTO @Test VALUES('Transaction Time','Numeric', 6, 0 ,68)
INSERT INTO @Test VALUES('Merchant Category Code','Numeric', 4 ,0 ,74)
INSERT INTO @Test VALUES('Card Acceptor Settlement Date','Numeric', 6, 0 ,78)
INSERT INTO @Test VALUES('Card Acceptor ID','Character', 15 ,'--', 84)
INSERT INTO @Test VALUES('Card Acceptor Terminal ID','Character', 8 ,'--', 99)
INSERT INTO @Test VALUES('Card Acceptor Terminal Location','Character', 40 ,'--', 107)
INSERT INTO @Test VALUES('Acquirer ID','Character', 11 ,'--', 147)
INSERT INTO @Test VALUES('Acquirer Settlement Date','Numeric', 6, 0, 158)
INSERT INTO @Test VALUES('Transaction Currency code','Character', 3 ,'--', 164)
INSERT INTO @Test VALUES('Transaction Amount','Numeric', 15, 0, 167)
INSERT INTO @Test VALUES('Actual Transaction Amount','Numeric', 15, 0, 182)
INSERT INTO @Test VALUES('Transaction Acitivity fee','Numeric', 15, 0 ,197)
INSERT INTO @Test VALUES('Acquirer settlement Currency Code','Character', 3 ,'--', 212)
INSERT INTO @Test VALUES('Acquirer settlement Amount','Numeric', 15, 0, 215)
INSERT INTO @Test VALUES('Acquirer Settlement Fee','Numeric', 15, 0, 230)
INSERT INTO @Test VALUES('Acquirer settlement processing fee','Numeric', 15, 0, 245)
INSERT INTO @Test VALUES('Transaction/Acquirer Conversion Rate','Numeric', 15, 9, 260)
DECLARE @Counter INT, @TotalCount INT, @StartPosition INT, @Length INT, @Description VARCHAR(100)
SET @Counter = 1
SET @TotalCount = (SELECT COUNT(*) FROM @Test)
DECLARE @Text VARCHAR(MAX)
SET @Text = 'LNS0502 734814472272006220181353200004002 714821114611599995171214140521601117121490068000000006894384001NEAR SEVEN HILL BRIDEGEJALNA ROAD MHIN810955 1712143560000000000000000000000000000000000000000000003560000000000000000000000000000000000000000000'
CREATE TABLE #Temp(Description VARCHAR(100),Text VARCHAR(50))
WHILE (@Counter <= @TotalCount)
BEGIN
SELECT @Description= Description, @StartPosition = Start_Position, @Length = Length FROM @Test WHERE Id = @Counter
INSERT INTO #Temp VALUES (@Description,SUBSTRING(@Text, @StartPosition, @Length))
SET @Counter = @Counter + 1
CONTINUE;
END
DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName = ISNULL(@ColumnName + ',','')+ QUOTENAME([Description]) FROM (SELECT DISTINCT [Description] FROM #Temp) AS t
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
SET @DynamicPivotQuery = 'SELECT *
FROM (
SELECT Description,Text
FROM #Temp
) p
PIVOT
(
MIN([Text]) FOR Description IN ('+@ColumnName+')
) AS pvt'
EXEC(@DynamicPivotQuery)
DROP TABLE #Temp
Out put will be the column names with the values.
