CODEWITHSUNDEEP
sqlsql-serverconcatenation
Natalia Natalie
Natalia Natalie

Reputation: 677

SQL Server New Column based on Concatenation

I have this query which gives this result:

 DECLARE 

 @CustomerID int = 8,
 @UtilityCompanyID int = 1

SELECT MD.SERV_ACCT, AD.ACCOUNT, MD.SERV_ADDRESS
FROM
(SELECT DISTINCT SERV_ACCT, SERV_ADDRESS, MAX(INV_DATE) as MAXINVDATE
FROM tblAPSData

WHERE SERV_ACCT NOT IN (SELECT ServiceAccount
            FROM tblMEP_Meters
            JOIN tblMEP_Sites
            ON tblMEP_Meters.SiteID = tblMEP_Sites.ID

            JOIN tblMEP_Projects
            ON tblMEP_Projects.ID = tblMEP_Sites.ProjectID

            WHERE CustomerID = 8
            AND Type = 1
            )

AND ACCOUNT IN (SELECT AccountNumber
                FROM tblMEP_CustomerAccounts
                WHERE CustomerID = @CustomerID
                AND UtilityCompanyID = @UtilityCompanyID)

AND INV_DATE > DATEADD(month, -6, getdate())
GROUP BY SERV_ACCT, SERV_ADDRESS) MD
join tblAPSdata AD on MD.SERV_ACCT = AD.SERV_ACCT 
and MD.MAXINVDATE = AD.INV_DATE

Result:

SERV_ACCT   ACCOUNT SERV_ADDRESS
0289S61288  117512280   5250 E MONTGOMERY RD          
1435S01282  117512280   5801 E SEVEN PALMS LN         
2498S21288  117512280   27880 N 64 ST                 
5538S21284  117512280   5802 E DOVE VALLEY RD         
6109S12286  117512280   5405 E PINNACLE VISTA DR      
7488S22289  117512280   5555 E PINNACLE VISTA DR      
8058S12287  117512280   5405 E PINNACLE VISTA DR      
9058S00288  117512280   33606 N 60 ST                 
9645S21281  117512280   4322 E DESERT WILLOW PKWY     
1440S13289  312937281   2610 N WEST ST                
5384S92284  979437282   1701 E PONDEROSA PKWY         
0492S90281  117512280   33424 N 60 ST                 
1303S90280  117512280   33401 N 56 ST                 
1548S00286  117512280   33606 N 60 ST                 
7358S00281  117512280   33606 N 60 ST                 
9168S00282  117512280   33016 N 60 ST

I want to create a new column that would be like this:

SERV_ACCT   ACCOUNT         SERV_ADDRESS
0289S61288  117512280   4324E MONT          0289S61288, 117512280, 4324E MONT 
1435S01282  117512280   432E SE             1435S01282, 117512280, 432E SE  
2498S21288  117512280   reN 64 ST           2498S21288, 117512280, reN 64 ST                
5538S21284  117512280   reE DOV             5538S21284, 117512280, reE DOV

And so on for the rest. Basically the last column will be SERV_ACCT, ACCOUNT, SERV_ADDRESS but I am not sure how to do it.

Upvotes: 0

Views: 59

Answers (2)

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Something like this.

SELECT MD.SERV_ACCT, AD.ACCOUNT, MD.SERV_ADDRESS,
    MD.SERV_ACCT + ', ' + AD.ACCOUNT ',  ' + MD.SERV_ADDRESS AS LasColumn
FROM ....

Upvotes: 0

sgeddes
sgeddes

Reputation: 62831

Just Use + for SQL Server:

SELECT MD.SERV_ACCT, AD.ACCOUNT, MD.SERV_ADDRESS, 
    MD.SERV_ACCT + ', ' + AD.ACCOUNT + ', ' + MD.SERV_ADDRESS
    ...

This assumes the data types are all varchar(). If not, you'll need to use CONVERT or CAST. For example, assuming Account is not a varchar():

MD.SERV_ACCT + ', ' + CAST(AD.ACCOUNT as Varchar(Max)) + ', ' + MD.SERV_ADDRESS

Upvotes: 2

Related Questions