split table into multiple tables based on not null criteria in sqlserver/ssis

Question

I have a scenario where i have an input table table (Dynamic Table number of columns are not fixed) like below and need to get multiple tables based on not null values

Input table

ID  Name    Mobile  Year    value
1   john    1238769 2001    35
2   tommy   3423456 2001    56
3   smith   8761934 2007    65
4   NULL    4783921 2005    78
5   robert  8549543 2008    18
6   mary    5648404 2011    40
7   NULL    6729113 2003    59
8   NULL    NULL    2006    10
9   cathy   NULL    2010    35
10  jessi   NULL    2012    45

So i need something like below tables based on not null

Output table1

ID  Name    Mobile  Year    value
1   john    1238769 2001    35
2   tommy   3423456 2001    56
3   smith   8761934 2007    65
5   robert  8549543 2008    18
6   mary    5648404 2011    40

output table 2

ID  Mobile  Year    value
4   4783921 2005    78
7   6729113 2003    59

output table3

ID  Name    Year    value
9   cathy   2010    3578
10  jessi   2012    45

and finally output table 4

ID  Year    value
8   2006    10

This input table is dynamically generated so the number of columns varies every time. — sra1, Apr 09 '13 at 17:37

score 0 · Answer 1 · answered Apr 09 '13 at 16:39

0

INSERT INTO OutputTable1
SELECT yourtable.*
FROM yourtable
WHERE Name IS NOT NULL and Mobile IS NOT NULL

INSERT INTO OutputTable2
SELECT yourtable.*
FROM yourtable
WHERE Name IS NULL and Mobile IS NOT NULL

INSERT INTO OutputTable3
SELECT yourtable.*
FROM yourtable
WHERE Name IS NOT NULL and Mobile IS NULL

INSERT INTO OutputTable4
SELECT yourtable.*
FROM yourtable
WHERE Name IS NULL and Mobile IS NULL

answered Apr 09 '13 at 16:39

fthiella

48,073
15
90
106

Hi fthiella, Thanks for the reply. but in my input table the number of columns and names are not constant it varies( it is generated dynamically every time, so for exampleon day one there can be 10 columns and on day to it can be 30 and another day it can be 110) so i cannot take the exact column name in where clause. Is there a way that this can be done? – sra1 Apr 09 '13 at 17:35
@user2262518 oh i see... i don't know how to do it in SQL Server but if you use MySql maybe there's a solution... let me think about it... – fthiella Apr 09 '13 at 18:47
if it is possible in Mysql please let me know the approach, that would help!! – sra1 Apr 09 '13 at 19:24
@user2262518 i was trying to solve this problem using just MySql, it is possible and it's also funny :) but it is really over complicated. It's much more simple to generate all the SQL queries you need using a programming language, you just need to find all combinations of NULL/NOT NULL of every column – fthiella Apr 09 '13 at 20:40

score 0 · Answer 2 · edited May 23 '17 at 10:25

Since the number of columns is unknown (as well as their names, probably), you'll have to use dynamic SQL. Assuming the input table has a PK and the PK's name is either known beforehand or can somehow be determined easily, here's one approach to solving the problem:

Query metadata to determine the table's column names.
Using the list of names, build and execute a query that would:
- unpivot all the data (non-PK) columns (converting them all into strings, apparently);
- group the unpivoted result set by the PK and GROUP_CONCAT the names into two lists:
  
  1) columns with non-null values, in the form of 'name1,name2,...', as SelectList,
  
  2) columns with null values, like this: 'name1 IS NULL AND name2 IS NULL AND ...', as NullCondition;
Using distinct SelectList and NullCondition values from the last result set and the following template, build a series of queries to retrieve data from the original table:
```
' SELECT ' + SelectList +
' FROM yourtable' +
' WHERE ' + NullCondition
```

I'm not sure in which of the two products (SQL Server or MySQL) it would be more convenient to implement the above. It is true that SQL Server doesn't have a dedicated aggregate concatenation function like MySQL's GROUP_CONCAT. Workarounds are not unknown, though. MySQL, on the other hand, doesn't support this very handy UNPIVOT clause that SQL Server has. But again, alternatives to using UNPIVOT exist as well.

split table into multiple tables based on not null criteria in sqlserver/ssis

2 Answers2