SSRS矩阵表报告。合并行

Question

我就是这样弄到桌子的。

+------+---------+-----------+---------+
| Cstmr| morning | afternoon |  night  |
+------+---------+-----------+---------+
| ab   | N/A     | N/A       |    car  |
|      | N/A     | N/A       |    11pm |
|      | bike    | N/A       |    N/A  |
|      | 9am     | N/A       |    N/A  |
|      | N/A     | cycle     |    N/A  |
|      | N/A     | 2pm       |    N/A  |
| ac   | N/A     | N/A       |    car  |
|      | N/A     | N/A       |    11pm |
|      | bike    | N/A       |    N/A  |
|      | 9am     | N/A       |    N/A  |
+------+---------+-----------+---------+

我希望它是这样显示的。

+------+---------+-----------+---------+
| Cstmr| morning | afternoon |  night  |
+------+---------+-----------+---------+
| ab   | bike    | cycle     |    car  |
|      | 9am     | 2pm       |    11pm |
| ac   | bike    | N/A       |    car  |
|      | 9am     |           |    11pm |
+------+---------+-----------+---------+

Cstmr是客户。上午六时至下午十二时下午12:00-10:00晚上:晚上10点至早上6点对于客户'ab'，上午9点提供自行车，下午2点提供自行车，晚上11点提供汽车。根据时间，数据应显示在各自的列中。如果在该时间内未提供车辆，则应显示N/A。

And this is how parent and child groups are formed.

Answer 1

我的天，从项目的角度来看，为什么这是一个好问题?当数据根本没有标准化的时候。

其次，“这就是我获得表格的方式”是什么意思。这就是实际的表，或者这是您在一些查询之后得到的结果集。

第三，尝试将此脚本与其他示例数据一起使用，并告诉我问题所在。如果上面的脚本是正确的，但速度很慢，那么我们可以为任何业务需求应用分页。

declare @t table(Cstmr varchar(20),morning varchar(20)
,afternoon varchar(20), night varchar(20))

insert into @t VALUES
('ab','NA','NA','car')
,( null, 'NA','NA','11pm ')
,( null, 'bike','NA','NA  ')
,( null, '9am','NA','NA  ')
,( null, 'NA','cycle','NA  ')
,( null, 'NA','2pm','NA  ')
,('ac','NA','NA','car  ')
,( null,'NA','NA','11pm ')
,( null,'bike','NA','NA  ')
,( null,'9am','NA','NA  ')

;

WITH CTE
AS (
    SELECT Cstmr
        ,morning
        ,afternoon
        ,night
        ,ROW_NUMBER() OVER (
            ORDER BY (
                    SELECT NULL
                    )
            ) rn
    FROM @t
    )
    ,CTE1
AS (
    SELECT cstmr
        ,morning
        ,afternoon
        ,night
        ,rn
        ,CASE 
            WHEN morning = 'NA'
                THEN 0
            WHEN isdate(morning) = 0
                THEN 1
            ELSE 2
            END rnm
        ,CASE 
            WHEN afternoon = 'NA'
                THEN 0
            WHEN isdate(afternoon) = 0
                THEN 1
            ELSE 2
            END rnaf
        ,CASE 
            WHEN night = 'NA'
                THEN 0
            WHEN isdate(night) = 0
                THEN 1
            ELSE 2
            END rnn
    FROM cte c
    WHERE rn = 1

    UNION ALL

    SELECT CASE 
            WHEN c.cstmr IS NULL
                THEN c1.cstmr
            ELSE c.cstmr
            END
        ,c.morning
        ,c.afternoon
        ,c.night
        ,c.rn
        ,CASE 
            WHEN c.morning = 'NA'
                THEN 0
            WHEN isdate(c.morning) = 0
                THEN 1
            ELSE 2
            END rnm
        ,CASE 
            WHEN c.afternoon = 'NA'
                THEN 0
            WHEN isdate(c.afternoon) = 0
                THEN 1
            ELSE 2
            END rnaf
        ,CASE 
            WHEN c.night = 'NA'
                THEN 0
            WHEN isdate(c.night) = 0
                THEN 1
            ELSE 2
            END rnn
    FROM cte c
    INNER JOIN cte1 c1 ON c.rn = c1.rn + 1
    )
SELECT m.Cstmr
    ,m.morning
    ,m.rnm
    ,a.afternoon
    ,n.night
FROM cte1 m
LEFT JOIN cte1 a ON a.cstmr = m.cstmr
    AND m.rnm = a.rnaf
LEFT JOIN cte1 n ON n.cstmr = m.cstmr
    AND m.rnm = n.rnn
WHERE m.morning <> 'NA'