SSRS报表设计求助：按人员分组展示SQL查询结果

Hey there! Let's work through getting your SQL query results grouped by person, matching that clean highlighted yellow report style you're aiming for. Here are practical approaches depending on whether you need to handle this in SQL itself, or use a reporting tool to polish the presentation:

1. Pure SQL: Aggregate Activity Details per Person #

If you just need to consolidate each person's activities into a single row (great for quick summaries), use string aggregation functions tailored to your SQL dialect. Let's assume your raw data lives in a table called person_activities with columns: person_name, activity, unit_price, additional_fee.

MySQL/MariaDB Example #

SELECT
  person_name,
  GROUP_CONCAT(
    CONCAT(activity, ' | 单价: ', unit_price, ' | 附加费: ', additional_fee)
    SEPARATOR '\n'
  ) AS activity_summary
FROM person_activities
GROUP BY person_name;

PostgreSQL Example #

SELECT
  person_name,
  STRING_AGG(
    CONCAT(activity, ' | 单价: ', unit_price, ' | 附加费: ', additional_fee),
    E'\n'
  ) AS activity_summary
FROM person_activities
GROUP BY person_name;

SQL Server Example #

SELECT
  person_name,
  STRING_AGG(
    CONCAT(activity, ' | 单价: ', unit_price, ' | 附加费: ', additional_fee),
    CHAR(10)
  ) AS activity_summary
FROM person_activities
GROUP BY person_name;

2. Reporting Tools: Merge Cells & Highlight Groups (Exact Match to Your Highlighted Style) #

If you need that clean merged-cell table with yellow highlights (like Excel's grouped view), SQL will handle fetching raw sorted data, and your reporting tool will take care of the formatting:

Excel Steps #

1. Run your base SQL query (no aggregation) to get all rows, sorted by person_name.
2. Import the results into Excel.
3. Select your data range → Go to Data > Subtotal.
   • Group by: person_name
   • Use function: Count (any function works—we just need the grouping structure)
4. Delete the auto-generated subtotal rows, then manually merge the person_name cells for each group.
5. Apply yellow fill to the merged person rows for that highlighted effect.

Power BI/Tableau Steps #

1. Import your sorted SQL data into the tool.
2. Create a matrix visualization:
   • Drag person_name to the Rows pane
   • Drag activity, unit_price, additional_fee to the Values pane
3. Enable row grouping/collapsing, then use conditional formatting to apply a yellow background to the person_name rows.

3. Application-Level Grouping (For Web/Apps) #

If you're displaying this in a custom app, fetch the sorted SQL data first, then handle grouping in your code. Here's a quick Python example using pandas:

import pandas as pd
import psycopg2  # Or your database connector of choice

# Connect to your database and fetch sorted data
conn = psycopg2.connect("your_db_connection_string")
df = pd.read_sql("SELECT * FROM person_activities ORDER BY person_name", conn)

# Group and print formatted output (easily adapted for HTML/UI rendering)
for person, group in df.groupby("person_name"):
    print(f"**{person}** (Apply yellow highlight here in your UI)")
    for _, row in group.iterrows():
        print(f"- Activity: {row['activity']} | Unit Price: {row['unit_price']} | Additional Fee: {row['additional_fee']}")

A quick heads-up: Pure SQL can't replicate merged cells or conditional highlighting directly—those are presentation-layer tasks. Focus on getting clean, sorted/grouped data from SQL, then use a tool or code to apply the formatting you need.

内容的提问来源于stack exchange，提问作者Blessed