0

I have the following query that runs in postgresql-9.6, I need to achieve the same output on a SQL Server DB.

Here is the query, I've replaced all fields from my DB with the string values that would come from them anyway (DB Fields are: "primary_key_fields", "primary_key_values", "table_name", "min_sequence"):

SELECT 
UNNEST(STRING_TO_ARRAY(demo.primary_key_fields, ',')) AS primary_key_fields,    
UNNEST(STRING_TO_ARRAY(demo.primary_key_values, ',')) AS primary_key_values, 
table_name, 
min_sequence, 
ROW_NUMBER() OVER(partition by demo.primary_key_fields) AS rn

FROM (
    SELECT
    'Name,surname,age,location,id' AS primary_key_fields,
    'Nash,Marley,27,South Africa,121' AS primary_key_values,
    'person' AS table_name, 
    '1' AS min_sequence

    UNION ALL
    SELECT
    'Name,surname,age,location,id' AS primary_key_fields,   
    'Paul,Scott,25,South America,999' AS primary_key_values, 
    'person' AS table_name, 
    '1' AS min_sequence
    ) demo

I'm expecting the following output:

Expected Output

Highly appreciate the assistance. I'm using SQL Server 2017.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Nash
  • 23
  • 7
  • SQL Server does not support arrays the way that Postgres does. Please add sample data to your question. – Tim Biegeleisen Sep 23 '19 at 07:29
  • What is this query supposed to do? Neither `UNNEST` and `STRING_TO_ARRAY` aren't part of SQL, they are vendor-specific functions. And even in PostgreSQL `ROW_Number` requires an ORDER BY, otherwise the row numbers returned will be arbitrary – Panagiotis Kanavos Sep 23 '19 at 07:30
  • That said, [STRING_SPLIT](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017) splits a string and returns individual elements as a table. That's probably the same as using `UNNEST(STRING_TO_ARRAY()`. Even in PostgreSQL though, storing *CSV values* instead of using proper columns is a serious design problem – Panagiotis Kanavos Sep 23 '19 at 07:33
  • 2
    This sounds like an [XY Problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). There was a problem X and the attempted solution Y was to concatenate key values and fields into strings. That's a very bad idea in any database - how are you going to *use* those values in queries? So when querying became impossible an attempt was made to split the strings instead of go back and fix the original problem. What is the *original* problem? – Panagiotis Kanavos Sep 23 '19 at 07:35
  • If it's about putting hardcoded data in a SQL for MS SQL Server then you should probably check if a select from values can help you. An SO example [here](https://stackoverflow.com/a/35093787/4003419) – LukStorms Sep 23 '19 at 07:45
  • Thanks for all the responses so far. I just need the desired output on SQL server dont mind if we not using arrays in the same way. Ive messed with the STRING_SPILT function and does most but not everything i need. As for the XY problem, its entirely plausible this is the case, but i cant change the original source i can only convert the code. This is used as part of another system process the DB is really just there to hold about 100 records at a time in this format. – Nash Sep 23 '19 at 07:46
  • @Nash there's no "just" about this query. I doubt it works for PostgreSQL except by accident. Are you trying to do [generate an element number](https://stackoverflow.com/questions/8760419/postgresql-unnest-with-element-number) so you can match fields and values? In PostgreSQL 9.4+ that's done with the `WITH ORDINALITY` clause. `ROW_NUMBER()` was used in earlier versions. This is a complex query even in PostgreSQL – Panagiotis Kanavos Sep 23 '19 at 07:52
  • @PanagiotisKanavos, the main problem with `STRING_SPLIT` is that the output rows might be in any order. The order is not guaranteed to match the order of the substrings in the input string. So, you can get `surname` next to `27` and `age` next to `South Africa`. Maybe, you could write your own CLR function that would split a string and return a table with two columns - first is the value, second is the ordinal number of that value. There should be plenty examples of a string split function for SQL Server versions before 2017. – Vladimir Baranov Sep 23 '19 at 09:03
  • @VladimirBaranov `UNNEST` is no different, which is why `WITH ORDINALITY` was introduced. If you check [this similar question](https://stackoverflow.com/questions/8760419/postgresql-unnest-with-element-number) `ROW_NUMBER()` gives no guarantee either. The original query itself has issues. – Panagiotis Kanavos Sep 23 '19 at 09:09
  • @PanagiotisKanavos, well, then `SPRING_SPLIT` is the way to go. It will "achieve the same result as the postgres query", namely, undefined order, the same as in the given Postgres query. ;-) But, seriously, Nash, you need to find or write your own a function that splits a string into a table **and preserves somehow the information about the order of the values in the original delimited string**. – Vladimir Baranov Sep 23 '19 at 10:38

1 Answers1

0

No longer needed. This question can be closed. No solution was found, changed the source system to accomdate what was needed.

Nash
  • 23
  • 7