Search code examples
sqljsonodbc

Renaming columns within a Select SQL

I am having issues with renaming columns within a select.

Here is the original SQL command

SELECT 
"id",
"1",
"3_3",
"3_6",
"4",
"5",
"6"
FROM "entries_without_parent" WITH( meta='TileTech_Meta_For_Contact_Forms' )

Here is the command I tried

SELECT 
"id",
"1",
FROM "entries_without_parent" WITH( meta='TileTech_Meta_For_Contact_Forms' ),
Select 
"3_3", 
"3_6" as 'FullName'
FROM "entries_without_parent" WITH( meta='TileTech_Meta_For_Contact_Forms' ),

SELECT
"4",
"5",
"6"

FROM "entries_without_parent" WITH( meta='TileTech_Meta_For_Contact_Forms' )

I need to merge 3_3 and 3_6 and call it Full Name

but then I would like to also rename all the columns into something that isn't a number like 1 to Email.

I am fairly new to SQL but I've gotten this fair, to say the least.

The original command works but when I tried to modify the SQL command I get errors such as this one

Ln3. col 98: Syntax error. expected: CONNECTION.).UNION.:

Send help!

Solution

  • I agree with you that the numeric column names on that table are the pits.

    You wrote

    SELECT "3_3", 
       "3_6" AS 'FullName'
FROM "entries_without_parent" ...

    (Well, you wrote several SELECTs, but let's just stick to one, and avoid any UNION ALL craziness.)

    I don't recognize that WITH meta syntax, and you didn't specify which DB vendor you're using, so I'm just going to ignore it and go with SQL-92.

    You are using quoting I do not recognize, such as "3_6". I will ignore that, and will prefer the standard backtick quoting for column names that don't start with alpha, that don't look like an identifier. Note that some vendors use [ ] square brackets to quote such column names.

    Use this:

    SELECT `id`,
       `3_3` AS first_name,
       `3_6` AS last_name
FROM entries_without_parent ...

    The two-character id actually needs no quoting, as it looks like an alphanumeric identifier that starts with alpha, starts with i. I just quoted it for consistency -- feel free to strip those spurious quotes.

    Additionally, you wish to combine certain columns. Use this:

    SELECT id,
       CONCAT(`3_3`, " ", `3_6`) AS full_name ...

    Some DB vendors let you catenate strings with vertical bar notation:

    SELECT id,
       `3_3` || " " || `3_6` AS full_name ...

    I invite you to Read The Fine Manual for details.