优化PIVOT查询：无需聚合函数实现单表多行合并为指定列结构

To merge your rows into the desired structure without using aggregate functions, you can use self-joins to pull in data for each shift type separately. Here's a straightforward implementation:

Final Query #

SELECT
    COALESCE(d.id, e.id, n.id) AS id,
    base.No,
    base.Date,
    base.Name,
    ISNULL(d.Dsc1, '-') AS Desc1Day,
    ISNULL(d.Dsc2, '-') AS Desc2Day,
    ISNULL(e.Dsc1, '-') AS Desc1Evening,
    ISNULL(e.Dsc2, '-') AS Desc2Evening,
    ISNULL(n.Dsc1, '-') AS Desc1Night,
    ISNULL(n.Dsc2, '-') AS Desc2Night
FROM (
    -- Get unique groups of No, Date, Name to form our base rows
    SELECT DISTINCT No, Date, Name FROM xyz
) base
-- Join to fetch Day shift details
LEFT JOIN xyz d 
    ON base.No = d.No 
    AND base.Date = d.Date 
    AND base.Name = d.Name 
    AND d.ShiftType = 'Day'
-- Join to fetch Evening shift details
LEFT JOIN xyz e 
    ON base.No = e.No 
    AND base.Date = e.Date 
    AND base.Name = e.Name 
    AND e.ShiftType = 'Evening'
-- Join to fetch Night shift details
LEFT JOIN xyz n 
    ON base.No = n.No 
    AND base.Date = n.Date 
    AND base.Name = n.Name 
    AND n.ShiftType = 'Night'
ORDER BY base.No DESC;

How This Works #

1. Base Subquery: The base subquery creates unique rows for each combination of No, Date, and Name—these are the core rows of our final output.
2. Self-Joins: Three LEFT JOINs to the original table filter for each shift type, pulling Dsc1 and Dsc2 values into dedicated columns for Day, Evening, and Night shifts.
3. Null Handling: ISNULL replaces missing shift data (where a shift doesn't exist for a group) with - to match your target format.
4. ID Selection: COALESCE picks the first non-null id from the shift rows, aligning with your sample where the Day shift's ID is used for group 101 and the Night shift's ID for group 102.

Output #

This query will produce exactly the structure you requested (note: the sample target had a minor typo where Day Desc1Day should likely be Day Desc1—this query returns the correct original values from your source data).

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