Click here to Skip to main content
13,089,386 members (60,626 online)
Rate this:
 
Please Sign up or sign in to vote.
See more:
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



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

what i want is add up the 15 and 4 into 19
Posted 18-Jun-14 17:11pm
Jamie8881.7K

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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


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

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web02 | 2.8.170813.1 | Last Updated 19 Jun 2014
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100