Click here to Skip to main content
11,485,294 members (76,642 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2008R2
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 18:11pm
abel831.2K

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
0 Sergey Alexandrovich Kryukov 330
1 Maciej Los 280
2 Raje_ 155
3 Mehdi Gholam 150
4 OriginalGriff 133
0 Sergey Alexandrovich Kryukov 8,494
1 OriginalGriff 7,948
2 Sascha Lefèvre 3,199
3 Maciej Los 2,681
4 Richard Deeming 2,370


Advertise | Privacy | Mobile
Web04 | 2.8.150520.1 | Last Updated 19 Jun 2014
Copyright © CodeProject, 1999-2015
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