将多行值合并为一个 [英] Combine multiple rows value into one

问题描述

我可以知道如何将多行值组合成一个吗？以下是我的代码：

Hi, may i know how to combine multiple rows value into one? Below are my codes:

DECLARE @SQL NVARCHAR(MAX)
DECLARE @params NVARCHAR(MAX)

SELECT @SQL = 'SELECT [0] as Pending, [1] as Save_As_Draft, [2] as Spec_Confirmed, [3] as Request_Revision, [4] as Withdraw, [5] as Pending_RND, [6] as Pending_QC, [7] as Manday_Confirmed, [0]+[1]+[2]+[3]+[4]+[5]+[6]+[7] as Total
FROM
(
    SELECT P.DocStatus,P.CustomerID,YEAR(createdate) as specYear
    FROM dbo.CRF_Project P
    INNER JOIN dbo.CustomerList C
    ON P.CustomerID = C.CustomerID
) p

PIVOT
(
    COUNT (CustomerID) FOR DocStatus IN ([0], [1], [2], [3], [4], [5], [6], [7])

) AS pvt '


SELECT @params = '@YEAR VARCHAR(MAX)'

IF(@YEAR != ' ')
    SELECT @SQL = @SQL + ' WHERE specYear = ' + @YEAR +''

SELECT @SQL = @SQL

EXEC sp_executesql @SQL, @params, @YEAR
END

我现在得到的输出是：

待定

=======

15

4



我是什么想要把15和4加成19

the output i get now for example is:
Pending
=======
15
4

what i want is add up the 15 and 4 into 19

推荐答案

DECLARE @SQL NVARCHAR(MAX)
DECLARE @params NVARCHAR(MAX)


SELECT @SQL = 'SELECT [0] as Pending, [1] as Save_As_Draft, [2] as Spec_Confirmed, [3] as Request_Revision, [4] as Withdraw, [5] as Pending_RND, [6] as Pending_QC, [7] as Manday_Confirmed, [0]+[1]+[2]+[3]+[4]+[5]+[6]+[7] as Total
FROM
(
    SELECT P.DocStatus, YEAR(createdate) as specYear
    FROM dbo.CRF_Project P
    INNER JOIN dbo.CustomerList C
    ON P.CustomerID = C.CustomerID
) p

PIVOT
(
    COUNT (DocStatus) FOR DocStatus IN ([0], [1], [2], [3], [4], [5], [6], [7])

) AS pvt '


IF(@YEAR != ' ')
    SELECT @SQL = @SQL + ' WHERE specYear = ' + @YEAR +''

ELSE
    SELECT @SQL = 'SELECT [0] as Pending, [1] as Save_As_Draft, [2] as Spec_Confirmed, [3] as Request_Revision, [4] as Withdraw, [5] as Pending_RND, [6] as Pending_QC, [7] as Manday_Confirmed, [0]+[1]+[2]+[3]+[4]+[5]+[6]+[7] as Total
    FROM
    (
        SELECT P.DocStatus
        FROM dbo.CRF_Project P
        INNER JOIN dbo.CustomerList C
        ON P.CustomerID = C.CustomerID
    ) p

    PIVOT
    (
        COUNT (DocStatus) FOR DocStatus IN ([0], [1], [2], [3], [4], [5], [6], [7])

    ) AS pvt '

SELECT @params = '@YEAR VARCHAR(MAX)'
SELECT @SQL = @SQL

EXEC sp_executesql @SQL, @params, @YEAR
END

将2个条件拆分为2个sql语句，一个用where子句，一个没有where子句。以及子查询中的SELECT。

split the 2 conditions into 2 sql statement, one with where clause and one without where clause. And also the 'SELECT' in subquery.

这篇关于将多行值合并为一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！