DEV Community

Judy
Judy

Posted on

1 1 1 1 1

Uncertain number but regular column to row conversion:From SQL to SPL#5

The MS SQL database has an externally generated non-standard table that generates N pairs of fields and one record at a time. Each pair of field names is divided into two parts separated by underscores, with the first half being the same but unknown, and the second half being fixed name and age.

Image description
Now we need to combine the first half of each pair of field names and their corresponding field values into one record, for a total of N records. You can first convert this record from column to row, and then concatenate every two records into one row.

Image description
SQL Solution:

SELECT V.id,
       MAX(CASE V.subkey WHEN N'name' THEN OJ.value END) AS name,
       MAX(CASE V.subkey WHEN N'age' THEN OJ.value END) AS age
FROM (SELECT *
      FROM dbo.tb YT
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) J(JSON)
     CROSS APPLY OPENJSON (J.JSON) OJ
     CROSS APPLY (VALUES(LEFT(OJ.[key],NULLIF(CHARINDEX('_',OJ.[Key]),0)-1), STUFF(OJ.[Key],1,NULLIF(CHARINDEX('_',OJ.[Key]),0),'')))V(id,subkey)
GROUP BY V.id;
SELECT V.id,
       MAX(CASE V.subkey WHEN N'name' THEN OJ.value END) AS name,
       MAX(CASE V.subkey WHEN N'age' THEN OJ.value END) AS age
FROM (SELECT *
      FROM dbo.tb YT
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) J(JSON)
     CROSS APPLY OPENJSON (J.JSON) OJ
     CROSS APPLY (VALUES(LEFT(OJ.[key],NULLIF(CHARINDEX('_',OJ.[Key]),0)-1), STUFF(OJ.[Key],1,NULLIF(CHARINDEX('_',OJ.[Key]),0),'')))V(id,subkey)
GROUP BY V.id;

Enter fullscreen mode Exit fullscreen mode

SQL has the pivot function that can perform column to row conversion, but column names must be written out. Dynamically generating column names would be very complex. Here, we can only change the thinking. First, convert the record into a JSON string, then take multiple "field names: field values" separately, and then use cross join to spell multiple records. The code difficulty is high. The transposition of SQL is very inflexible, so here we use the max... group by method to indirectly implement it, and the code is also a bit verbose.

SPL code is much simpler and easier to understand:

Image description
A1: Load data.

A2: Use pivot function to convert column to row, no need to write column names. The new two-dimensional table has 6 records and 2 fields, with field col storing the original field name and row storing the original field value.

A3: Simple implementation of grouping every 2 rows, and the grouping subsets can be retained without aggregation. # is the row number, \ is division to round.

A4: Get values from each group of records by position to form a new two-dimensional table. ~1 represents the first record within the group.

SPL is open source and free, welcome to try it, it will bring you a different surprise!

Open source address

Free download

Image of Timescale

Timescale – the developer's data platform for modern apps, built on PostgreSQL

Timescale Cloud is PostgreSQL optimized for speed, scale, and performance. Over 3 million IoT, AI, crypto, and dev tool apps are powered by Timescale. Try it free today! No credit card required.

Try free

Top comments (0)

Billboard image

Try REST API Generation for Snowflake

DevOps for Private APIs. Automate the building, securing, and documenting of internal/private REST APIs with built-in enterprise security on bare-metal, VMs, or containers.

  • Auto-generated live APIs mapped from Snowflake database schema
  • Interactive Swagger API documentation
  • Scripting engine to customize your API
  • Built-in role-based access control

Learn more

👋 Kindness is contagious

Engage with a sea of insights in this enlightening article, highly esteemed within the encouraging DEV Community. Programmers of every skill level are invited to participate and enrich our shared knowledge.

A simple "thank you" can uplift someone's spirits. Express your appreciation in the comments section!

On DEV, sharing knowledge smooths our journey and strengthens our community bonds. Found this useful? A brief thank you to the author can mean a lot.

Okay