user2171512 user2171512 - 17 days ago 9
SQL Question

Concatenate two fields with case condition

How can i concatenate 2 fields but i have also CASE condition. Here is my sql:

INSERT INTO #TempTable([Name],[TotalBets])
SELECT CASE
WHEN @Limited ='All'
THEN DPL.[FirstName]
WHEN @Limited ='Blind_No'
THEN DPL.[FirstName]
ELSE '*****'
END AS DPL.[FirstName] + ' ' +
CASE
WHEN @Limited ='All'
THEN DPL.[LastName]
WHEN @Limited ='Blind_No'
AND [BlindAccount] = 1
THEN '*****'
WHEN @Limited ='Blind_No'
AND [BlindAccount] = 0
THEN DPL.[LastName]
ELSE '*****'
END AS DPL.[LastName] [Name],
SUM(FWA.TotalBetAmount) [TotalBets]
FROM [WarehouseMgmt].[FactWalletAgr] FWA
JOIN [WarehouseMgmt].[DimPlayer] DPL ON FWA.[PlayerId] = DPL.[Id]
JOIN [WarehouseMgmt].[DimGame] DG ON FWA.[GameId] = DG.[Id]
GROUP BY DPL.[FirstName] + ' ' + DPL.[LastName]


But this query is wrong, and i don't know how to concatenate when i have CASE condition.
Any help or suggestion ?

Answer

You can not use alias names in group by clause. I have changed it like below. If you are using above versions of sql server 2008 then use concat function. Doing concatenation b/w two strings will results to NULL some times.

INSERT INTO #TempTable([Name],[TotalBets])
    SELECT     ( CASE
                    WHEN @Limited ='All'
                        THEN DPL.[FirstName]
                    WHEN @Limited ='Blind_No'                   
                        THEN DPL.[FirstName]            
                    ELSE '*****'
                    END) + ' ' + 
               ( CASE
                    WHEN @Limited ='All'
                        THEN DPL.[LastName]
                    WHEN @Limited ='Blind_No'
                        AND [BlindAccount] = 1
                        THEN '*****'
                    WHEN @Limited ='Blind_No'
                        AND [BlindAccount] = 0
                        THEN DPL.[LastName]
                    ELSE '*****'
                    END ) [Name],
            SUM(FWA.TotalBetAmount)                     [TotalBets]     
        FROM [WarehouseMgmt].[FactWalletAgr] FWA
        JOIN [WarehouseMgmt].[DimPlayer] DPL ON FWA.[PlayerId] = DPL.[Id]
        JOIN [WarehouseMgmt].[DimGame] DG ON FWA.[GameId] = DG.[Id] 
    GROUP BY    ( CASE
                    WHEN @Limited ='All'
                        THEN DPL.[FirstName]
                    WHEN @Limited ='Blind_No'                   
                        THEN DPL.[FirstName]            
                    ELSE '*****'
                    END) + ' ' + 
               ( CASE
                    WHEN @Limited ='All'
                        THEN DPL.[LastName]
                    WHEN @Limited ='Blind_No'
                        AND [BlindAccount] = 1
                        THEN '*****'
                    WHEN @Limited ='Blind_No'
                        AND [BlindAccount] = 0
                        THEN DPL.[LastName]
                    ELSE '*****'
                    END )