How to combine rows based on field value

Question

I have designed the following query

SELECT 
v.visitid,
CASE 
   WHEN vd.DocType = 1 THEN 'y' ELSE 'n'
END as 'FinalReportAttached'
,CASE 
   WHEN vd.DocType = 13 THEN 'y' ELSE 'n'
END as 'InspectorReportAttached'
,CASE 
   WHEN vd.DocType = 2 THEN 'y' ELSE 'n'
END as 'Co-ordReportAttached'

FROM Visits v
INNER JOIN VisitDocs vd on vd.VisitID = v.VisitID  
WHERE v.VisitID  = 79118

enter image description here

I like to show results in one row. If report is there then 'y' and if not then 'n' .

There is a one to manay relation b/w visit and visitdoc table. Visitdoc can have many different docs for one visit. I need to check if visitdoc has doctype 1,3 or 12 against each visit then say yes otherwise no.

visitID |FinalReport |InspectorReport |Co-ordReport 
------------------------------------------------
79118   |n           |y               |y

If the rows has one column different how you will group them by visitID? — kostas ch., Apr 25 '14 at 10:57
possible duplicate of [Get ROWS as COLUMNS (SQL Server dynamic PIVOT query)](http://stackoverflow.com/questions/12074939/get-rows-as-columns-sql-server-dynamic-pivot-query) — Panagiotis Kanavos, Apr 25 '14 at 10:59

score 1 · Answer 1 · answered Apr 25 '14 at 10:59

To get one row, use aggregation functions:

SELECT vd.visitid,
       MAX(CASE WHEN vd.DocType = 1 THEN 'y' ELSE 'n' END) as FinalReportAttached,
       MAX(CASE WHEN vd.DocType = 13 THEN 'y' ELSE 'n' END) as InspectorReportAttached,
       MAX(CASE WHEN vd.DocType = 2 THEN 'y' ELSE 'n' END) as [Co-ordReportAttached]
FROM VisitDocs vd
WHERE vd.VisitID  = 79118;

Note that this works because 'y' > 'n' (at least in the character collations that I am familiar with).

I also changed the query in two other ways. I removed the single quotes from the column aliases. Only use single quotes for string and date constants. Using single quotes for identifiers can lead to confusion. Also, the join doesn't seem to be needed, because the original query used an inner join and no other fields from v.

score 0 · Answer 2 · answered Apr 25 '14 at 11:00

Please try:

select 
    visitid, 
    MAX(FinalReportAttached)  FinalReportAttached, 
    MAX(InspectorReportAttached) InspectorReportAttached, 
    MAX([Co-ordReportAttached]) [Co-ordReportAttached]
from(
    SELECT 
    v.visitid,
    CASE 
       WHEN vd.DocType = 1 THEN 'y' ELSE 'n'
    END as 'FinalReportAttached'
    ,CASE 
       WHEN vd.DocType = 13 THEN 'y' ELSE 'n'
    END as 'InspectorReportAttached'
    ,CASE 
       WHEN vd.DocType = 2 THEN 'y' ELSE 'n'
    END as 'Co-ordReportAttached'

    FROM Visits v
    INNER JOIN VisitDocs vd on vd.VisitID = v.VisitID  
    WHERE v.VisitID  = 79118
)x
group by visitid

No reason to do this, SQL Server 2008 has the PIVOT command. Besides, calling `MAX` on a text value can produce unexpected results — Panagiotis Kanavos, Apr 25 '14 at 11:00

score 0 · Accepted Answer · answered Apr 25 '14 at 11:00

You could try this:

SELECT 
v.visitid,
CASE 
   WHEN SUM(CASE WHEN vd.DocType = 1 THEN 1 ELSE 0 END)>0 THEN 'y' ELSE 'n'
END as 'FinalReportAttached'
,CASE 
   WHEN SUM(CASE WHEN vd.DocType = 13 THEN 1 ELSE 0 END)>0 THEN 'y' ELSE 'n'
END as 'InspectorReportAttached'
,CASE 
   WHEN SUM(CASE WHEN vd.DocType = 2 THEN 1 ELSE 0 END)>0 THEN 'y' ELSE 'n'
END as 'Co-ordReportAttached'
FROM Visits v
INNER JOIN VisitDocs vd on vd.VisitID = v.VisitID  
WHERE v.VisitID  = 79118
GROUP BY v.VisitID

score 0 · Answer 4 · answered Apr 25 '14 at 11:01

This will do the trick for you

SELECT 
v.visitid,
CASE 
   WHEN sum(case when vd.DocType = 1  THEN 1 ELSE 0 END) = 1 THEN 'y' ELSE 'n'
END as 'FinalReportAttached'
,CASE 
   WHEN sum(case when vd.DocType = 13  THEN 1 ELSE 0 END) = 1 THEN 'y' ELSE 'n'
END as 'InspectorReportAttached'
,CASE 
   WHEN sum(case when vd.DocType = 2  THEN 1 ELSE 0 END) = 1 THEN THEN 'y' ELSE 'n'
END as 'Co-ordReportAttached'

FROM Visits v
INNER JOIN VisitDocs vd on vd.VisitID = v.VisitID  
WHERE v.VisitID  = 79118
GROUP BY v.visitId

score 0 · Answer 5 · answered Apr 25 '14 at 11:12

SELECT 
    v.visitid,
MAX(CASE 
        WHEN vd.DocType = 1 THEN 'y' ELSE 'n'
    END) as 'FinalReportAttached'
,MAX(CASE 
        WHEN vd.DocType = 13 THEN 'y' ELSE 'n'
    END) as 'InspectorReportAttached'
,MAX(CASE 
        WHEN vd.DocType = 2 THEN 'y' ELSE 'n'
    END) as 'Co-ordReportAttached'
FROM Visits v
INNER JOIN VisitDocs vd 
    on vd.VisitID = v.VisitID  
WHERE v.VisitID  = 79118
group by v.VisitID

qxg · Answer 6 · 2014-04-25T15:21:43.463

0

Why no one uses EXISTS. Ok, it's a bit long.

SELECT 
v.visitid,
CASE 
   WHEN EXISTS (SELECT * FROM VisitDocs vd WHERE vd.VisitID = v.VisitID AND vd.DocType = 1) THEN 'y' ELSE 'n'
END AS 'FinalReportAttached'
,CASE 
   WHEN EXISTS (SELECT * FROM VisitDocs vd WHERE vd.VisitID = v.VisitID AND vd.DocType = 13) THEN 'y' ELSE 'n'
END AS 'InspectorReportAttached'
,CASE 
   WHEN EXISTS (SELECT * FROM VisitDocs vd WHERE vd.VisitID = v.VisitID AND vd.DocType = 2) THEN 'y' ELSE 'n'
END AS 'Co-ordReportAttached'
FROM Visits v
WHERE v.VisitID  = 79118

edited Apr 25 '14 at 15:21

answered Apr 25 '14 at 12:02

qxg

6,955
1
28
36

your sql query is not correct plus i think it will cost more as compare to other ones. – user1263981 Apr 25 '14 at 14:29
I see the error. Regarding cost, I disagree. Other aggregation functions require scanning all docs of certain visit, keeping internal expression and checking if count is 0. EXISTS returns as soon as it sees the first qualified row. It's much faster. – qxg Apr 25 '14 at 15:25

How to combine rows based on field value

6 Answers6