0

What will be the MySQL code for below code in SQL Server.

 SELECT @cols = STUFF((SELECT ',' + QUOTENAME(col+cast(rn as Nvarchar(max)))
   from
    (
     SELECT row_number() over(partition by 
    TPID,EAPID,locale,Origin_airport,               
    Origin_multicity_gaia_id,dest_multicity_gaia_id_1,
    dest_multicity_gaia_id_2,dest_multicity_gaia_id_3,dest_airport_1,
                   dest_airport_2,dest_airport_3,test_keys
                   order by Origin_airport,test_keys) rn 
                   from ##New_Generic         
          ) d
                cross apply
                ( 
                  SELECT 'dest_city_1_image_url_1', 'First' union all
                  SELECT 'dest_city_2_image_url_1', 'Second' union all
                  SELECT 'dest_city_3_image_url_1', 'Third' 


                ) c (col, so)
                group by col, rn, so
                order by rn, so
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

closed as too broad by mustaccio, SqlWorldWide, Erik Darling, hot2use, McNets Oct 16 '17 at 6:54

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    Which part of the code? There's a lot going on there. – Erik Darling Oct 13 '17 at 13:49
  • 1
    Window functions, CROSS APPLY, XML PATH, STUFF, QUOTENAME. At least 5 things that there is no direct equivalent in MySQL. It will be probably be easier to explain what this code does (to users that speak "mysql") and find a way to do it in MySQL than directly "translate" all of this wall - which would need someone who speaks both "sql-server" and "mysql". – ypercubeᵀᴹ Oct 13 '17 at 13:53
  • I use both SQL Server and MySQL and my advice would be to not bother trying to convert it. Start from scratch in MySQL until you have your desired output - thats the approach I have always had to use when the source query has SQL Server-specific items or uses items that MySQL just doesn't support even when they are part of the SQL standard. – Mr.Brownstone Oct 13 '17 at 14:11
  • 1
    Windowing functions have been added in MariaDB 10.2 and MySQL 8.0.3. – Rick James Oct 13 '17 at 20:16
2

At a guess - STUFF, QUOTENAME, and FOR XML PATH are being used to build an aggregated string out of the inner query - specifically, to build a column list for use in a later dynamic query. MySQL uses backticks (`) to "quote" DB object names, and there's a GROUP_CONCAT aggregate function built-in.

The CROSS APPLY isn't actually using anything from the query to determine its values - it appears to be a hard-coded sub-query, in which case you should be able to just put it in as a CROSS JOIN instead.

You can search this site or use your favorite search engine to find out how to do row numbers in MySQL. In fact, here's an answer I provided a while ago that shows you how to generate a row number that would've been done with a PARTITION BY in MS SQL.

Basically, you set up a variable to hold the row number. For each row, the variable's starting value will be the number of the previous row. You also store the current value of each partition column. If any of the partition columns change, set the row number variable to 1, otherwise, add one to it. Note that the query (or subquery) you generate the row number in must be sorted first by all the partition columns, then by the columns that would be in the OVER clause's ORDER BY using the MS SQL functions.

The one thing I can't see a way around easily is your use of a global temporary table (##NewGeneric). Technically that's not really a part of this code (it assumes it already exists and just uses it), but it's something you should at least be aware of. In MS SQL Server, a global temp table is created in one session, goes away when that session either drops it or closes (mostly), but allows other sessions to access it (and, if another session is using it when it should go away, it holds off until that session's current statement (transaction?) is over).

If you are really having other sessions access it, you'll have to use a regular table. The biggest differences should be that you have to be sure to drop it yourself, and you won't be able to drop it immediately if it's involved in a current transaction; your session will have to wait around until it's free to drop it.

Not the answer you're looking for? Browse other questions tagged or ask your own question.