[mysql] MySQL - Rows to Columns

This isn't the exact answer you are looking for but it was a solution that i needed on my project and hope this helps someone. This will list 1 to n row items separated by commas. Group_Concat makes this possible in MySQL.

select
cemetery.cemetery_id as "Cemetery_ID",
GROUP_CONCAT(distinct(names.name)) as "Cemetery_Name",
cemetery.latitude as Latitude,
cemetery.longitude as Longitude,
c.Contact_Info,
d.Direction_Type,
d.Directions

    from cemetery
    left join cemetery_names on cemetery.cemetery_id = cemetery_names.cemetery_id 
    left join names on cemetery_names.name_id = names.name_id 
    left join cemetery_contact on cemetery.cemetery_id = cemetery_contact.cemetery_id 

    left join 
    (
        select 
            cemetery_contact.cemetery_id as cID,
            group_concat(contacts.name, char(32), phone.number) as Contact_Info

                from cemetery_contact
                left join contacts on cemetery_contact.contact_id = contacts.contact_id 
                left join phone on cemetery_contact.contact_id = phone.contact_id 

            group by cID
    )
    as c on c.cID = cemetery.cemetery_id


    left join
    (
        select 
            cemetery_id as dID, 
            group_concat(direction_type.direction_type) as Direction_Type,
            group_concat(directions.value , char(13), char(9)) as Directions

                from directions
                left join direction_type on directions.type = direction_type.direction_type_id

            group by dID


    )
    as d on d.dID  = cemetery.cemetery_id

group by Cemetery_ID

This cemetery has two common names so the names are listed in different rows connected by a single id but two name ids and the query produces something like this

    CemeteryID     Cemetery_Name             Latitude
    1                    Appleton,Sulpher Springs   35.4276242832293

Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to pivot-table

Use Excel pivot table as data source for another Pivot Table PivotTable's Report Filter using "greater than" How to SUM parts of a column which have same text value in different column in the same row Use formula in custom calculated field in Pivot Table refresh both the External data source and pivot tables together within a time schedule Convert Rows to columns using 'Pivot' in SQL Server Ordering issue with date values when creating pivot tables Python Pandas : pivot table with aggfunc = count unique distinct PivotTable to show values, not sum of values Simple Pivot Table to Count Unique Values

Examples related to etl

C# Dictionary get item by index What does 'killed' mean when a processing of a huge CSV with Python, which suddenly stops? How do I fix 'Invalid character value for cast specification' on a date column in flat file? MySQL - Rows to Columns Import Excel spreadsheet columns into SQL Server database

Examples related to crosstab

Postgres - Transpose Rows to Columns PostgreSQL Crosstab Query MySQL - Rows to Columns