mysql - 将 SQL Server 查询结果格式化为 3D 直方图

Question

我有一个像这样的表，用于存储 3D 直方图数据。

CREATE TABLE [dbo].[HISTOGRAM_3D](
    [H3D_X] [decimal](6, 2) NULL,
    [H3D_Y] [decimal](6, 2) NULL,
    [H3D_Z] [decimal](6, 2) NULL
)

我使用此查询来获取直方图的数据:

SELECT h3d.H3D_X, H3D_Y, SUM(h3d.H3D_Z) AS VALUE 
    FROM HISTOGRAM_3D h3d
    GROUP BY h3d.H3D_X, H3D_Y
    ORDER BY h3d.H3D_X ASC

现在，我得到的是这样的:

H3D_X | H3D_Y | VALUE
H3D_X | H3D_Y | VALUE
H3D_X | H3D_Y | VALUE
H3D_X | H3D_Y | VALUE
....

但是，我想将结果格式化为以下格式，因为使用 MS Excel 显示图形非常容易。

---------------------- | ---- H3D_X VALUES -----------
H3D_Y VALUES | VALUE | VALUE | VALUE  ......
H3D_Y VALUES | VALUE | VALUE | VALUE  ......
H3D_Y VALUES | VALUE | VALUE | VALUE  ......
H3D_Y VALUES | VALUE | VALUE | VALUE  ......

可以用 SQL 实现吗？

Answer 1

解决了!一种可能的解决方案是使用如下动态查询:

声明@cols为NVARCHAR(MAX)， @query_histogram_3d AS NVARCHAR(MAX)

select @cols = STUFF((SELECT',' + QUOTENAME(H3D_X) 
                FROM SL_HISTOGRAM_3D
                WHERE H3D_CL_ID = @CYCLE_ID
                GROUP BY H3D_X
                ORDER BY H3D_X
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

set @query_histogram_2d = N'SELECT H3D_CL_ID, H3D_Y, ' + @cols + N' FROM 
            (
            SELECT H3D_CL_ID, H3D_X, H3D_Y, SUM(H3D_Z) AS VALUE
            FROM SL_HISTOGRAM_3D
            WHERE H3D_CL_ID = ' + CONVERT(varchar(20),@CYCLE_ID) + N'
            GROUP BY H3D_CL_ID, H3D_X, H3D_Y
        ) x
        PIVOT 
        (
            MAX(VALUE)
            FOR H3D_X IN (' + @cols + N')
        ) p '

exec sp_executesql @query_histogram_3d;

结束

谢谢!