How to transform first row as column name?

Question

I hope you are all well. I would like your help on a data transformation task that I have. I would like to convert the first row of a table to a column name

I am working on SQL Server Azure and I get daily data from another service.

This service loads a table that is of the same form.

and I would like to transform the data in the same manner

Do You have any idea how to do it ?

Answer 1

Another possible approach is to transform your text data into valid JSON arrays and then use OPENJSON() with an explicit schema and dynamic statement.

Working example:

Input:

CREATE TABLE #Data (
   RowNum int,
   Line nvarchar(max)
)
INSERT INTO #Data
   (RowNum, Line)
VALUES
   (1, 'ColumnA;ColumnB;ColumnC'),
   (2, 'ValueA1;ValueB1;ValueC1'),
   (3, 'ValueA2;ValueB2;ValueC2'),
   (4, 'ValueA3;ValueB3;ValueC3'),
   (5, 'ValueA4;ValueB4;ValueC4'),
   (6, 'ValueA5;ValueB5;ValueC5')

T-SQL:

-- Explicit schema generation
DECLARE @schema nvarchar(max)
SELECT @schema = STUFF((
   SELECT CONCAT(N',', j.[value], N' nvarchar(max) ''$[', j.[key], N']''')
   FROM #Data d
   CROSS APPLY OPENJSON(CONCAT(N'["', REPLACE(d.Line, ';', '","'), N'"]')) j
   WHERE d.RowNum = 1
   FOR XML PATH('')
), 1, 1, N'')

-- Dymanic statement
DECLARE @stm nvarchar(max)
SET @stm = CONCAT(
   N'SELECT j.* FROM #Data d ',
   N'CROSS APPLY OPENJSON(CONCAT(N''[["'', REPLACE(d.Line, '';'', ''","''), N''"]]'')) ',
   N'WITH (',
   @schema,
   N') j WHERE d.RowNum > 1'
)

-- Execution
EXEC sp_executesql @stm

Output:

-----------------------
ColumnA ColumnB ColumnC
-----------------------
ValueA1 ValueB1 ValueC1
ValueA2 ValueB2 ValueC2
ValueA3 ValueB3 ValueC3
ValueA4 ValueB4 ValueC4
ValueA5 ValueB5 ValueC5

Explanations:

The main part is to transform each row's data into valid JSON arrays. The count of the columns can be different.

Data from the first row will be used for explicit schema generation and values ColumnA;ColumnB;ColumnC are transformed into ["ColumnA","ColumnB","ColumnC"]. Values from subsequent rows ValueA1;ValueB1;ValueC1 are transformed into [["ValueA1","ValueB1","ValueC1"]].

Next simple examples demonstrate how OPENJSON() returns data with default and explicit schema:

With default schema:

DECLARE @json nvarchar(max)
SET @json = '["ValueA1", "ValueB1", "ValueC1"]'
SELECT *
FROM OPENJSON(@json) 

Output for default schema:

----------------
key value   type
----------------
0   ValueA1 1
1   ValueB1 1
2   ValueC1 1

With explicit schema:

SET @json = '[["ValueA1", "ValueB1", "ValueC1"]]'
SELECT *
FROM OPENJSON(@json) 
WITH (
   ColumnA nvarchar(max) '$[0]',
   ColumnB nvarchar(max) '$[1]',
   ColumnC nvarchar(max) '$[2]'
)

Output for explicit schema:

-----------------------
ColumnA ColumnB ColumnC
-----------------------
ValueA1 ValueB1 ValueC1

Answer 2

The way to solve this is by using a little dynamic SQL magic:

First, create and populate sample table (Please save us thus step in your future questions):

DECLARE @T As Table
(
    Row_num int,
    Line nvarchar(4000)
);

INSERT INTO @T (Row_Num, Line) VALUES
(1, 'Col1;Col2;Col3'),
(2, 'Val1;Val2;Val3'),
(3, 'Value1;Value2;Value1'),
(4, 'Val A; val B;Val A'),
(5, 'Value A; Value B;Value C');

Then, build a union all query that selects the values from every row but the first, replacing the semicolon (;) separator with a comma (,) surrounded by apostrophes ('). Add an apostrophe before and after the string (which means we are treating all the data as strings):

DECLARE @Sql nvarchar(max) = '';

SELECT @Sql += 'UNION ALL SELECT '''+ REPLACE(Line, ';', ''',''') + ''' '
FROM @T 
WHERE Row_Num > 1;

Next, use stuff to replace the first UNION ALL with a common table expression declaration, specifying the column names in the declaration itself. Note that here we don't need the apostrophes anymore, just to replace the semicolon with a comma:

SELECT @Sql = STUFF(@Sql, 1, 10, 'WITH CTE('+ REPLACE(Line, ';', ',') +') AS (') + ') SELECT * FROM CTE'
FROM @T 
WHERE Row_Num = 1;

Finally, execute the sql:

EXEC(@Sql)

Results:

Col1        Col2        Col3
Val1        Val2        Val3
Value1      Value2      Value1
Val A        val B      Val A
Value A      Value B    Value C

You can see a live demo on rextester.