sql query to display the input data

I want a SQL query to display the following data

Input Data:

ID  GroupID     Data
 1     1        Hello
 2     1        Null
 3     1        Null
 4     1        World
 5     2        Niladri
 6     2        XXX
 7     2        Null
 8     2        PPP
 9     2        Null
10     2        Null
11     2        Null
12     2        LLL

as

Output Data:

GroupID开发者_高级运维   MergedData
   1          Hello2World   
   2          NiladriXXX1PPP3LLL

I need to group the data on GroupID and display the result as Hello2World

-->Hello is related to GroupID 1
-->2 is count of NULLS
-->World is related to GroupID 1

Similarly for GroupID 2.

Kindly suggest?

Thanks

As you did not mention your DBMS, this is a solution for PostgreSQL:

SELECT groupid, 
       string_agg(temp_data,'')
FROM (
  SELECT id,
         groupid, 
         data,
         CASE 
           WHEN data IS NULL 
              THEN cast(max(rn) over (partition by groupid, data) as varchar)
           ELSE data
         END AS temp_data,
         row_number() over (partition by groupid, data) as group_rn
  FROM (
    SELECT id,
           groupid,
           data,
           CASE 
             WHEN data IS NULL 
                THEN row_number() over (partition by groupid,data)
             ELSE NULL
           END AS rn
    FROM foo
  ) t1
  ORDER BY id
) t2 
WHERE group_rn in (0,1)
GROUP BY groupid

If your DBMS supports ANSI windowing functions and has something similar like the string_agg() function then this should be portable to your DBMS as well.