Server删除空值

Question

在下面的SQL中需要帮助是我的代码

CREATE VIEW OpenPO_DIM (Sales_Line, Dim_ID, EMP, BT, RC, DIV)
AS
    SELECT  dbo.[ $Sales Line].[Document No_], --SL.no
            dbo.[ $Dimension Set Entry].[Dimension Set ID], --Dim_ID
            (SELECT       
                CASE
                    WHEN [ $Dimension Set Entry].[Dimension Code] = 'EMP' 

                    THEN [ $Dimension Value].Name 
                END 
            ), --EMP
            (SELECT       
                CASE
                    WHEN [ $Dimension Set Entry].[Dimension Code] = 'BT' 

                    THEN [ $Dimension Value].Name 
                END 
            ), --BT
            (SELECT       
                CASE
                    WHEN [ $Dimension Set Entry].[Dimension Code] = 'RC' 

                    THEN [ $Dimension Value].Name 
                END 
            ), --RC     
            (SELECT       
                CASE
                    WHEN [ $Dimension Set Entry].[Dimension Code] = 'DIV' 

                    THEN [ $Dimension Value].Name 
                END 
            ) --DIV

    FROM   dbo.[ $Dimension Value] 
            INNER JOIN dbo.[ $Dimension Set Entry] 
            ON dbo.[ $Dimension Value].[Dimension Code] = dbo.[ $Dimension Set Entry].[Dimension Code] 
            AND dbo.[ $Dimension Value].Code = dbo.[ $Dimension Set Entry].[Dimension Value Code]
            INNER JOIN dbo.[ $Sales Line] 
            ON dbo.[ $Dimension Set Entry].[Dimension Set ID] = dbo.[ $Sales Line].[Dimension Set ID]
    where  dbo.[ $Sales Line].[Document No_] LIKE N'%EG-SO%'

我得到了输出，如下面的截图所示

​

​

我希望返回的结果如下所示：

​

​

任何帮助都将不胜感激。

谢谢!

Answer 1

DECLARE @sample TABLE
(
    DocumentNo INT,
    SetId INT,
    DimCode VARCHAR(10),
    DimValue VARCHAR(10)
);
INSERT @sample
(
    DocumentNo,
    SetId,
    DimCode,
    DimValue
)
VALUES
(1, 1, 'EMP', 'EMPName1'),
(1, 1, 'BT', 'BTName1'),
(1, 1, 'RC', 'RCName1'),
(1, 1, 'DIV', 'DIVName1'),
(2, 1, 'EMP', 'EMPName2'),
(2, 1, 'BT', 'BTName2'),
(2, 1, 'RC', 'RCName2'),
(2, 1, 'DIV', 'DIVName2'),
(3, 1, 'EMP', 'EMPName3'),
(3, 1, 'BT', 'BTName3'),
(3, 1, 'RC', 'RCName3'),
(3, 1, 'DIV', 'DIVName3'),
(4, 1, 'EMP', 'EMPName4'),
(4, 1, 'BT', 'BTName4'),
(4, 1, 'RC', 'RCName4'),
(4, 1, 'DIV', 'DIVName4');

SELECT *
FROM @sample
    PIVOT
    (
        MAX(DimValue)
        FOR [DimCode] IN ([EMP], [BT], [RC], [DIV])
    ) pvt;