2

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.

  • Which version of SQL Server are you using? You'll need some string aggregation as well as a full outer join between the two tables. – Colin 't Hart Mar 11 '15 at 9:47
  • I'm using SQL Server 2008 – borisp Mar 11 '15 at 10:21
1
;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)
  • 1
    I tested both your script and mine and yours runs much faster. – BuahahaXD Mar 11 '15 at 9:51
0

A script to create the temporary tables:

 CREATE TABLE #Diagnostics (
    DATE DATE
    ,Diagnostics NVARCHAR(40)
    ,Id INT
    )

CREATE TABLE #Evaluation (
    DATE DATE
    ,Evaluation NVARCHAR(40)
    ,Id INT
    )

INSERT INTO #Diagnostics
VALUES (
    '20150308'
    ,'Diag1'
    ,2932
    )
    ,(
    '20150309'
    ,'Diag2'
    ,2932
    )

INSERT INTO #Evaluation
VALUES (
    '20150309'
    ,'Eval1'
    ,2932
    )
    ,(
    '20150309'
    ,'Eval2'
    ,2932
    )
    ,(
    '20150310'
    ,'Eval3'
    ,2932
    )

The select query:

SELECT DISTINCT coalesce(d1.DATE, e1.DATE) AS DATE
    ,coalesce((
            SELECT d2.Diagnostics + ','
            FROM #Diagnostics d2
            WHERE d2.DATE = coalesce(d1.DATE, e1.DATE)
            FOR XML PATH('')
            ), '') AS Diag
    ,coalesce((
            SELECT e2.Evaluation + ','
            FROM #Evaluation e2
            WHERE e2.DATE = coalesce(d1.DATE, e1.DATE)
            FOR XML PATH('')
            ), '') AS Eval
FROM #Diagnostics d1
FULL JOIN #Evaluation e1 ON d1.DATE = e1.DATE
    AND d1.Id = e1.Id
0

I have used temp tables to build an example of your schema so I can test the query. You can substitute your table names in where I have used the temp tables.

DECLARE @Evals TABLE
(Date Date
,Eval NVARCHAR(50))

INSERT INTO @Evals VALUES ('2015-03-09', 'Eval1'), ('2015-03-09', 'Eval2'), ('2015-03-10', 'Eval3')

DECLARE @diags TABLE
(Date Date
,Diags NVARCHAR(50))

INSERT INTO @diags VALUES ('2015-03-08', 'Diag0'), ('2015-03-09', 'Diag1'), ('2015-03-09', 'Diag2'), ('2015-03-10', 'Diag3')


SELECT  a.Date, 
        (
            SELECT (d.Diags + ',')
            FROM    @diags AS d
            WHERE   d.Date = a.Date
            FOR XML PATH('')
        ) AS Diagnostics,
        (
            SELECT (e.Eval + ',')
            FROM    @Evals AS e
            WHERE   e.Date = a.Date
            FOR XML PATH('')
        ) AS Evaluation

FROM (  
        SELECT Date 
        FROM @Evals 
        UNION 
        SELECT Date 
        FROM @diags) AS a

GROUP BY a.Date

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.