SQL Server – Select from Two Tables and Group by Dates

sql serversql-server-2008

I'm new in databases so I need help. I have two tables:

First table:

| Date .........| Diagnostics ................| ID ...............|

| 2015-03-08 ...| Diag1 ......................| 2932 .............|

| 2015-03-09 ...| Diag2 ......................| 2932 .............|

Second table:

| Date ..........| Evaluation .................|ID ................|

| 2015-03-09 ....| Eval1 ......................| 2932 .............|

| 2015-03-09 ....| Eval2 ......................| 2932 .............|

| 2015-03-10 ....| Eval3 ......................| 2932 .............|

And this is what I want – 1 table, rows grouped by dates , 2 columns where id, in this case, is 2932 :

| Date ..........| Diagnostics ...............| Evaluation ...............|

| 2015-03-08 ....| Diag1 .....................| ..........................|

| 2015-03-09 ....| Diag2 .....................| Eval1, Eval2 .............|

| 2015-03-10 ....| ...........................| Eval3 ....................|

I have tried to do all kind of joins, unions etc. and just couldn't get right result.

Best Answer

;WITH First_table 
AS (SELECT '2015-03-08' AS [Date],'Diag1' AS Diagnostics, 2932 AS ID
    UNION ALL SELECT '2015-03-09','Diag2', 2932
    )
,Second_table
AS (SELECT '2015-03-09' AS [Date], 'Eval1' AS Evaluation ,2932 AS ID
    UNION ALL SELECT '2015-03-09','Eval2',2932
    UNION ALL SELECT '2015-03-10' ,'Eval3' , 2932)


SELECT
    KT.[Date] AS [Date]
    ,FT.Diagnostics AS [Diagnostics]
    ,STUFF(ST.Evaluation,1,1,'') AS [Evaluation]
FROM
    (
        SELECT [Date] ,[ID]
        FROM First_table
        --GROUP BY [Date] ,[ID]

        UNION 

        SELECT [Date] ,[ID]
        FROM Second_table
        --GROUP BY [Date] ,[ID]
    ) AS KT  --keyTAble
    LEFT JOIN First_table AS FT
        ON kt.[Date] = FT.[Date]
        AND kt.[ID]= FT.[ID]
    OUTER APPLY
        (
            SELECT 
                ',' + Evaluation
            FROM Second_table AS ST
            WHERE 
                KT.[DATE] =ST.[Date]
                AND kT.[ID]= ST.[ID]
            FOR XML PATH('')
        )ST(Evaluation)
Related Question