Select query to get groupwise data result in mysql ?

If you prefer to implement this in C# , here's how you can do it:

using System;
using System.Collections.Generic;
using System.Linq;

class Program
{
    static void Main()
    {
        // Simulated Data (Replace this with actual database query results)
        var data = new List<(string Barcode, string SwitchPrice, int PSGID, string PSGroupName)>
        {
            ("A01", "Price2", 1, "LUNCH"),
            ("A02", "Price3", 1, "LUNCH"),
            ("A03", "Price4", 1, "LUNCH"),
            ("A04", "Price3", 2, "EVENING")
        };

        // Group by PSGroupName
        var groupedData = data.GroupBy(d => d.PSGroupName);

        // Print the Desired Output Format
        foreach (var group in groupedData)
        {
            Console.WriteLine($"PSGROUPName: {group.Key}");
            foreach (var item in group)
            {
                Console.WriteLine($"{item.Barcode,-8} {item.SwitchPrice,-10} {item.PSGID}");
            }
            Console.WriteLine(); // Add a blank line between groups
        }
    }
}

PSGROUPName: LUNCH
A01      Price2     1
A02      Price3     1
A03      Price4     1

PSGROUPName: EVENING
A04      Price3     2

1. SQL Query :

   • Your SQL query is correct and fetches all the necessary data. The formatting issue cannot be resolved purely in SQL.

2. Post-Processing :

   • Use a programming language like Python or C# to process and format the data after fetching it from the database.

3. Scalability :

   • This approach works well for small to medium datasets. For very large datasets, consider optimizing the grouping logic or processing the data in chunks.

If you want to fetch the data in a single query, your existing SQL query is already correct:

SELECT S.Barcode, S.SwitchPrice, S.PSGID, G.PSGroupName
FROM da5.SwitchPrice AS S
INNER JOIN da5.SwitchPriceGroup AS G ON S.PSGID = G.PSGID;

You can use this query to fetch the data and then process it programmatically

To achieve the desired output where the PSGROUPName acts as a header and the corresponding rows are grouped under it, you need to process the query result programmatically. SQL alone does not support this kind of hierarchical or formatted output directly. However, you can achieve this by using a programming language like Python, C#, or even SQL with some post-processing logic.

Below is an example implementation in Python using pandas to format the data as required:

import pandas as pd

# Sample Data (Simulating your SQL Query Result)
data = {
    "Barcode": ["A01", "A02", "A03", "A04"],
    "SwitchPrice": ["Price2", "Price3", "Price4", "Price3"],
    "PSGID": [1, 1, 1, 2],
    "PSGroupName": ["LUNCH", "LUNCH", "LUNCH", "EVENING"]
}

# Create a DataFrame
df = pd.DataFrame(data)

# Group by PSGroupName
grouped = df.groupby("PSGroupName")

# Print the Desired Output Format
for group_name, group_data in grouped:
    print(f"PSGROUPName: {group_name}")
    # Select only the required columns for display
    group_data = group_data[["Barcode", "SwitchPrice", "PSGID"]]
    print(group_data.to_string(index=False))
    print()  # Add a blank line between groups

PSGROUPName: LUNCH
 Barcode SwitchPrice  PSGID
 A01        Price2      1
 A02        Price3      1
 A03        Price4      1

PSGROUPName: EVENING
 Barcode SwitchPrice  PSGID
 A04        Price3      2

1. Input Data :

   • The input data is simulated using a dictionary and converted into a pandas.DataFrame. In a real-world scenario, you would fetch this data from your database using a library like sqlalchemy or pyodbc.

2. Grouping :

   • The groupby function groups the data by the PSGroupName column.

3. Formatting :

   • For each group, the group name (PSGroupName) is printed as a header.
   • The corresponding rows are displayed below the header, showing only the required columns (Barcode, SwitchPrice, PSGID).

4. Blank Line :

   • A blank line is added between groups for better readability.

@ Daniel Wright, 

Thanks for your response ., 

we require Groupname as header , details in the 2nd row like that 

Thanks

Based on the provided scenario, it seems like you are looking for a way to group the results based on the "PSGroupName" from your query. To achieve the desired result, you can utilize the `GROUP BY` clause in MySQL. Here is an example query that should help you achieve the desired output:

SELECT 
    S.Barcode, 
    S.SwitchPrice, 
    S.PSGID,
    G.PSGroupName
FROM 
    da5.SwitchPrice as S 
INNER JOIN 
    da5.SwitchPriceGroup as G 
ON 
    S.PSGID = G.PSGID
GROUP BY 
    G.PSGroupName,
    S.Barcode,
    S.SwitchPrice,
    S.PSGID;

In this query, we have included the `GROUP BY` clause to group the results based on the "PSGroupName". By including all the selected columns along with the grouping column "PSGroupName", you can get the desired output where the results are grouped under each "PSGroupName".

This query should help you achieve the result structure you desire, with the data grouped under the respective "PSGroupName". If you have any further questions or need additional clarification, feel free to ask.