CODEWITHSUNDEEP
sql-serverunpivot
SuperSimmer 44
SuperSimmer 44

Reputation: 999

Reformat existing table from paired columns into rows

For example I have a table with 5 rows and 7 columns, I wish to move the last two columns into the previous two columns. New format of table would now be 10 rows and 5 columns

Present Table format 

+-----+------------+----------+------------+---------------+------------+---------------+
| id  | VisitDate  | fkFamily | child1.DOB | child1.Gender | child2.DOB | child2.Gender |
+-----+------------+----------+------------+---------------+------------+---------------+
|  78 | 19/04/2010 |      277 | 14/03/2009 | 0             | NULL       | NULL          |
|  79 | 20/04/2010 |      289 | 12/08/2007 | 0             | NULL       | NULL          |
| 107 | 20/04/2010 |      191 | NULL       | NULL          | NULL       | NULL          |
| 108 | 20/04/2010 |      259 | NULL       | NULL          | 31/03/2010 | 1             |
| 109 | 20/04/2010 |      126 | NULL       | NULL          | NULL       | NULL          |
+-----+------------+----------+------------+---------------+------------+---------------+

New table format 

+-----+------------+----------+------------+----------------------+
| id  | VisitDate  | fkFamily | child.DOB  | child.Gender         |
+-----+------------+----------+------------+----------------------+
|  78 | 19/04/2010 |      277 | 14/03/2009 | 0                    |
|  79 | 20/04/2010 |      289 | 12/08/2007 | 0                    |
| 107 | 20/04/2010 |      191 | NULL       | NULL                 |
| 108 | 20/04/2010 |      259 | NULL       | NULL                 |
| 109 | 20/04/2010 |      126 | NULL       | NULL                 |
|  78 | 19/04/2010 |      277 | NULL       | NULL                 |
|  79 | 20/04/2010 |      289 | NULL       | NULL                 |
| 107 | 20/04/2010 |      191 | NULL       | NULL                 |
| 108 | 20/04/2010 |      259 | 31/03/2010 | 1                    |
| 109 | 20/04/2010 |      126 | NULL       | NULL                 |
+-----+------------+----------+------------+----------------------+

Upvotes: 1

Views: 68

Answers (2)

Mat Richardson
Mat Richardson

Reputation: 3606

You could reformat the table into something like this by using UNION:-

SELECT * FROM (
SELECT id, VisitDate, fkFamily, child1_DOB as child_DOB, child1_Gender as child_Gender
FROM yourtable
UNION
SELECT id, VisitDate, fkFamily, child2_DOB, child2_Gender
FROM yourtable) as temp

FIDDLE

You could use SELECT INTO if you wanted to create a new table from the results, for example:-

SELECT * INTO yournewtable FROM (
    SELECT id, VisitDate, fkFamily, child1_DOB as child_DOB, child1_Gender as child_Gender
    FROM yourtable
    UNION
    SELECT id, VisitDate, fkFamily, child2_DOB, child2_Gender
    FROM yourtable) as temp

Upvotes: 1

Taryn
Taryn

Reputation: 247810

You can get the final result by unpivoting the columns Child1_DOB, Child1_Gender, etc. Starting in SQL Server 2005, the unpivot function was made available but for your case I'd actually use CROSS APPLY so you can unpivot the Child1, and Child2 values in pairs.

The syntax would be:

select 
  t.id,
  t.visitdate,
  t.fkFamily,
  c.child_DOB,
  c.child_Gender
from yourtable t
cross apply
(
  select child1_DOB, child1_Gender union all
  select child2_DOB, child2_Gender
) c (child_DOB, child_Gender);

See SQL Fiddle with Demo

Then you could also include an identifier for each of the values so you know if it belonged to child one or two:

select 
  t.id,
  t.visitdate,
  t.fkFamily,
  c.child,
  c.child_DOB,
  c.child_Gender
from yourtable t
cross apply
(
  select 'Child1', child1_DOB, child1_Gender union all
  select 'Child2', child2_DOB, child2_Gender
) c (child, child_DOB, child_Gender)

See SQL Fiddle with Demo. These give a result similar to:

|  ID |  VISITDATE | FKFAMILY |  CHILD_DOB | CHILD_GENDER |
|-----|------------|----------|------------|--------------|
|  78 | 19/04/2010 |      277 | 14/03/2009 |            0 |
|  78 | 19/04/2010 |      277 |     (null) |       (null) |
|  79 | 20/04/2010 |      289 | 12/08/2007 |            0 |
|  79 | 20/04/2010 |      289 |     (null) |       (null) |
| 107 | 20/04/2010 |      191 |     (null) |       (null) |
| 107 | 20/04/2010 |      191 |     (null) |       (null) |
| 108 | 20/04/2010 |      259 |     (null) |       (null) |
| 108 | 20/04/2010 |      259 | 31/03/2010 |            1 |
| 109 | 20/04/2010 |      126 |     (null) |       (null) |
| 109 | 20/04/2010 |      126 |     (null) |       (null) |

Upvotes: 1

Related Questions