SQL query - Adding blank data for few columns

Question

Got below table structure.

Table1

ServiceID Activity_type       Start_date  End_date
1         Long Day Care       12/03/2012  12/03/2020
1         Family Day Care     12/03/2012  12/03/2020
2         Vacation Care       12/05/2012  12/03/2020
2         Before School Care  12/05/2012  12/03/2020
3         Long Day Care       12/09/2012  12/03/2020
3         Vacation Care       12/09/2012  12/03/2020
3         After School Care   12/09/2012  12/03/2020

Now I want to display blank Activity_type when service doesn't have Activity like get all the distinct Activity_type and if the service don't have that activity then display the record with ServiceId , Start_date, End_Date and a blank Activity_type. I want total rows for each service = total count of distinct Activity type.

Output should be like below. for each service has five rows as total count of distinct activity type is 5 here.

ServiceID Activity_type       Start_date  End_date
1         Long Day Care       12/03/2012  12/03/2020
1         Family Day Care     12/03/2012  12/03/2020
1                             12/03/2012  12/03/2020
1                             12/03/2012  12/03/2020
1                             12/03/2012  12/03/2020
2         Vacation Care       12/05/2012  12/03/2020
2         Before School Care  12/05/2012  12/03/2020
2                             12/05/2012  12/03/2020
2                             12/05/2012  12/03/2020
2                             12/05/2012  12/03/2020
3         Long Day Care       12/09/2012  12/03/2020
3         Vacation Care       12/09/2012  12/03/2020
3         After School Care   12/09/2012  12/03/2020
3                             12/09/2012  12/03/2020
3                             12/09/2012  12/03/2020

Any help???

Joachim Isaksson · Accepted Answer · 2013-05-08 04:24:17Z

1

I'm sure it could be made in a more efficient way, but the simplest way should be to make a cartesian product between the ServiceID and Activity_Type to get all combinations, and then LEFT JOIN to the data table to get the values that actually exist.

In TSQL, that would be;

SELECT s.ServiceID, ISNULL(t.Activity_Type,'') Activity_Type, 
       s.Start_date, s.End_date
FROM (SELECT DISTINCT ServiceID,Start_date, End_date FROM Table1) s
JOIN (SELECT DISTINCT Activity_Type FROM Table1) a
  ON 1=1
LEFT JOIN Table1 t
  ON s.ServiceID = t.ServiceID
 AND a.Activity_Type = t.Activity_Type
ORDER BY ServiceID, Activity_Type DESC

The only really RDBMS dependent part is ISNULL that for example in MySQL is called IFNULL.

An SQLfiddle to test with.

answered May 8, 2013 at 4:24

Joachim Isaksson

182k28 gold badges298 silver badges308 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dave Mulligan · Accepted Answer · 2013-05-08 04:35:01Z

0

If the Activity_type column is defined as allowing null values, then you can just skip that value when doing your INSERT. For example, in MySQL:

INSERT INTO table1 (ServiceID, Start_date, End_date) values (1, '2012-12-03', '2020-12-03');

Other databases may have slightly different default formats for specifying a date, but the idea should be the same.

answered May 8, 2013 at 4:35

Dave Mulligan

1,6833 gold badges20 silver badges31 bronze badges

1 Comment

Dave Mulligan Over a year ago

@Sivakumar: it isn't the clearest question and it isn't obvious that there is more than one table involved. I decided to answer the simplest possible interpretation of the question, in case it turns out that that is all the OP is asking.

Collectives™ on Stack Overflow

SQL query - Adding blank data for few columns

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related