0

I have 2 tables (Application,Transaction).

Application 

 ID      ApplicationName TransactionID
 -------------------------------------
 1             A             100
 2             A             101 
 3             B             102
 4             B             103
  • TransactionID is unique.
  • Application can have multiple TransactionID's.

Transaction 

ID      TransactionID   RequestNumber
---------------------------------------
 1            100           RQ1000
 2            101           RQ1000
 3            102           RQ1001
 4            102           RQ1001
 5            102           RQ1002
  • Single TransactionID can have multiple RequestNumber
  • Multple TransactionID can have same RequestNumber

Now my question is: I want to retrieve data it should be like below 

ApplicationName  Count(RequestNumber)  RequestNumberList
--------------------------------------------------------
      A                 1                   RQ1000
      B                 2                   RQ1001,RQ1002
  • Count(RequestNumber) should be distinct RequestNumber for that application
  • RequestNumberList should be distinct RequestNumber which is comma separated

I can get required result without 3rd column using below query:

SELECT 
    ApplicationName, COUNT(DISTINCT RequestNumber) AS RequestNumber_Count
FROM 
    Application A
JOIN 
    Transaction T ON A.TransactionID = T.TransactionID
GROUP BY 
    ApplicationName

Result: 

ApplicationName  Count(RequestNumber)
-------------------------------------
      A                 1
      B                 2

With 3rd column I wrote like this:

SELECT 
    ApplicationName, 
    COUNT(DISTINCT RequestNumber) AS RequestNumber_Count,
    COALESCE(RequestNumber + ', ', '')
FROM 
    Application A
JOIN 
    Transaction T ON A.TransactionID = T.TransactionID
GROUP BY 
    ApplicationName, RequestNumber

Result:

ApplicationName  Count(RequestNumber)  RequestNumberList
---------------------------------------------------------
      A                 1                   RQ1000,
      B                 1                   RQ1001,
      B                 1                   RQ1002,

I need alteration to my 2nd query to get desired result

Thanks in advance.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Yashas
  • 31
  • 6
  • This has been asked and answered dozens and dozens of times. – Sean Lange Jun 14 '17 at 19:42
  • Possible duplicate of [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) – Sean Lange Jun 14 '17 at 19:43
  • I have query to fetch either of 2nd or 3rd column not both in same query..There is a difference between other question and this question..please check – Yashas Jun 14 '17 at 19:50
  • No...it is the exact same concept. Start with your first query that gets everything else. The using STUFF and FOR XML you will generate the comma delimited list just like the duplicate question. – Sean Lange Jun 14 '17 at 19:59
  • In the link you given.They are fetching multiple project ID's for the same employee.It can be achieved easily by using COALESCE. In my example I have two tables I wants to map Each application has how many distinct Request number with those request number in 2 tables with TransactionID as reference. When you use COALESCE or STUFF you should use both ApplicationName and RequestNumber in GroupBy this in turn won't produce desired result. – Yashas Jun 14 '17 at 20:59
  • I tried using STUFF ...it's throwing error RequestNumber is not a part of aggregate function. – Yashas Jun 14 '17 at 21:01
  • Can you share what you tried? I don't have time to wrestle with this today but I can probably help you with this tomorrow. – Sean Lange Jun 14 '17 at 21:12
  • Finally Achieved with the combination of GROUP BY,HAVING and STUFF CLAUSE. – Yashas Jun 14 '17 at 21:58
  • Thank you, Sean Lange for your support and concern. – Yashas Jun 14 '17 at 22:04

1 Answers1

0

Finally Achieved with the combination of JOIN,STUFF,GROUP BY and HAVING clause.

SELECT A.ApplicationName,COUNT(DISTINCT T.RequestNumber) AS RequestNumber_Count, RequestNumberList = STUFF(( SELECT ',' +B.RequestNumber FROM Application D JOIN Trnsaction B ON D.TransactionID=B.TransactionID GROUP BY D.ApplicationName,B.RequestNumber HAVING D.ApplicationName=A.ApplicationName FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') FROM Application A JOIN Trnsaction T ON A.TransactionID=T.TransactionID GROUP BY A.ApplicationName

Yashas
  • 31
  • 6