sql-server - How to CONCAT two table columns into one column in SQL Server

Question

I am new to SQL Server. I have a script. Please check the script first

SELECT
    WOtask.PK,
    WOPK,
    TaskNo,
    TaskAction = CASE
                    WHEN WOTask.AssetPK IS NOT NULL 
                       THEN '<b>' + Asset.AssetName + ' [' + Asset.AssetID + ']</b> ' + 
                          CASE
                             WHEN Asset.Vicinity IS NOT NULL 
                                  AND Asset.Vicinity <> '''' 
                                THEN RTRIM(Asset.Vicinity) + ': '
                             ELSE ''''
                          END + WOtask.TaskAction + CASE
                    WHEN CONVERT(varchar, ValueLow) IS NOT NULL AND
                      CONVERT(varchar, ValueHi) IS NOT NULL AND
                      Spec = 1 THEN  ' ('+ 'Range:'+ '' + CONVERT(VARCHAR,CONVERT(FLOAT,ISNULL(ValueLow,0))) + ' - ' + CONVERT(VARCHAR,CONVERT(FLOAT,ISNULL(ValueHi,0))) + ')'
                    ELSE ''
                  END
                ELSE WOtask.TaskAction + CASE
                    WHEN CONVERT(varchar, ValueLow) IS NOT NULL AND
                      CONVERT(varchar, ValueHi) IS NOT NULL AND
                      Spec = 1 THEN  ' ('+ 'Range:'+ '' + CONVERT(VARCHAR,CONVERT(FLOAT,ISNULL(ValueLow,0))) + ' - ' + CONVERT(VARCHAR,CONVERT(FLOAT,ISNULL(ValueHi,0))) + ')'
                    ELSE ''
                  END 
              END , 
    Rate,
    Measurement,
    Initials,
    Fail,
    Complete,
    Header,
    LineStyle,
    WOtask.Comments,
    WOtask.NotApplicable,
    WOTask.Photo1,
    WOTask.Photo2
FROM 
    WOtask WITH (NOLOCK)
LEFT OUTER JOIN 
    Asset WITH (NOLOCK) ON Asset.AssetPK = WOTask.AssetPK
LEFT OUTER JOIN 
    AssetSpecification ON AssetSpecification.PK = WOTask.AssetSpecificationPK
WHERE 
    (WOPK IN (SELECT WOPK
              FROM WO WITH (NOLOCK)
              LEFT OUTER JOIN Asset WITH (NOLOCK) ON Asset.AssetPK = WO.AssetPK
              LEFT OUTER JOIN AssetHierarchy WITH (NOLOCK) ON AssetHierarchy.AssetPK = WO.AssetPK
              WHERE WO.WOPK = 10939)
    )
ORDER BY 
    WOPK, TaskNo

and here is the output :

now my requirement is to concat TaskAction and Comment column into single column . i would like to give one example of my requirement : the column must show TaskAction value plus Comment: hi(comment column value) . i have been trying to put CONVERT Function for concat two columns but i couldn't get the result as i have been applying convert function to wrong place .

Please help me with some solution. Thanks in advance

标签： sql-serversql-server-2014

Answer 1

这是您应该使用的 SQL：

SELECT WOtask.PK
     , WOPK
     , TaskNo
     , TaskAction = CASE
                        WHEN WOtask.AssetPK IS NOT NULL THEN
                            '<b>' + Asset.AssetName + ' [' + Asset.AssetID + ']</b> ' + CASE
                                                                                            WHEN Asset.Vicinity IS NOT NULL
                                                                                                 AND Asset.Vicinity <> '''' THEN
                                                                                                RTRIM(Asset.Vicinity) + ': '
                                                                                            ELSE
                                                                                                ''''
                                                                                        END + WOtask.TaskAction
                            + CASE
                                  WHEN CONVERT(VARCHAR, ValueLow) IS NOT NULL
                                       AND CONVERT(VARCHAR, ValueHi) IS NOT NULL
                                       AND Spec = 1 THEN
                                      ' (' + 'Range:' + '' + CONVERT(VARCHAR, CONVERT(FLOAT, ISNULL(ValueLow, 0))) + ' - '
                                      + CONVERT(VARCHAR, CONVERT(FLOAT, ISNULL(ValueHi, 0))) + ')'
                                  ELSE
                                      ''
                              END
                        ELSE
                            WOtask.TaskAction
                            + CASE
                                  WHEN CONVERT(VARCHAR, ValueLow) IS NOT NULL
                                       AND CONVERT(VARCHAR, ValueHi) IS NOT NULL
                                       AND Spec = 1 THEN
                                      ' (' + 'Range:' + '' + CONVERT(VARCHAR, CONVERT(FLOAT, ISNULL(ValueLow, 0))) + ' - '
                                      + CONVERT(VARCHAR, CONVERT(FLOAT, ISNULL(ValueHi, 0))) + ')'
                                  ELSE
                                      ''
                              END
                    END
     , Rate
     , Measurement
     , Initials
     , Fail
     , Complete
     , Header
     , LineStyle
     , WOtask.Comments
     , WOtask.NotApplicable
     , WOtask.Photo1
     , WOtask.Photo2
     , ISNULL(
                 CASE
                     WHEN WOtask.AssetPK IS NOT NULL THEN
                         '<b>' + Asset.AssetName + ' [' + Asset.AssetID + ']</b> ' + CASE
                                                                                         WHEN Asset.Vicinity IS NOT NULL
                                                                                              AND Asset.Vicinity <> '''' THEN
                                                                                             RTRIM(Asset.Vicinity) + ': '
                                                                                         ELSE
                                                                                             ''''
                                                                                     END + WOtask.TaskAction
                         + CASE
                               WHEN CONVERT(VARCHAR, ValueLow) IS NOT NULL
                                    AND CONVERT(VARCHAR, ValueHi) IS NOT NULL
                                    AND Spec = 1 THEN
                                   ' (' + 'Range:' + '' + CONVERT(VARCHAR, CONVERT(FLOAT, ISNULL(ValueLow, 0))) + ' - ' + CONVERT(VARCHAR, CONVERT(FLOAT, ISNULL(ValueHi, 0)))
                                   + ')'
                               ELSE
                                   ''
                           END
                     ELSE
                         WOtask.TaskAction
                         + CASE
                               WHEN CONVERT(VARCHAR, ValueLow) IS NOT NULL
                                    AND CONVERT(VARCHAR, ValueHi) IS NOT NULL
                                    AND Spec = 1 THEN
                                   ' (' + 'Range:' + '' + CONVERT(VARCHAR, CONVERT(FLOAT, ISNULL(ValueLow, 0))) + ' - ' + CONVERT(VARCHAR, CONVERT(FLOAT, ISNULL(ValueHi, 0)))
                                   + ')'
                               ELSE
                                   ''
                           END
                 END
               , ''
             ) + ISNULL(WOtask.Comments, '') AS [Single Column]
FROM WOtask WITH (NOLOCK)
    LEFT OUTER JOIN Asset WITH (NOLOCK) ON Asset.AssetPK = WOtask.AssetPK
    LEFT OUTER JOIN AssetSpecification ON AssetSpecification.PK = WOtask.AssetSpecificationPK
WHERE (WOPK IN
       (
           SELECT WOPK
           FROM WO WITH (NOLOCK)
               LEFT OUTER JOIN Asset WITH (NOLOCK) ON Asset.AssetPK = WO.AssetPK
               LEFT OUTER JOIN AssetHierarchy WITH (NOLOCK) ON AssetHierarchy.AssetPK = WO.AssetPK
           WHERE WO.WOPK = 10939
       )
      )
ORDER BY WOPK
       , TaskNo;