Breakdown (un-group by) multiple column in SQL

Question

My new data is grouped by from it's source, so is there any code that can breakdown or un-group the data then assign its value based on what column it was grouped.

the table looks like this

| age | education | city | male | female

| 28  | Secondary |  x   |  0   |   2
| 28  | University|  x   |  1   |   2
| 29  | Primary   |  y   |  1   |   0

i want the code do something like this

| age | education | city | gender |

| 28  | Secondary |  x   | female |
| 28  | Secondary |  x   | female | 
| 28  | University|  x   |  male  |
| 28  | University|  x   | female |
| 28  | University|  x   | female |
| 29  | Primary   |  y   |  male  |

Presumably, you're unable to alter the query that gives you the first result set? — Martin, Jan 29 '19 at 09:41
Hi i think you have to use un pivot see this link : https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017 https://stackoverflow.com/questions/19055902/unpivot-with-column-name — Sanpas, Jan 29 '19 at 09:45

Yogesh Sharma · Answer 1 · 2019-01-29T09:57:24.807

You want recursive CTE with APPLY :

with cte as (
     select age, education, city, mf, gender, 1 as val
     from table t cross apply
          ( values (male, 'male'), (female, 'female')
          ) tt (mf, gender)
     where mf > 0
     union all
     select age, education, city, mf, gender, val + 1
     from cte c
     where mf > val
)

select age, education, city, gender 
from cte c
order by age, education, city;

By default it has 100 recursion level, if you have a more gender recursion then you might need to use option (maxrecursion 0).

score 2 · Answer 2 · answered Jan 29 '19 at 10:02

The simple way would be UNION ALL with CROSS APPLY

SELECT M.age,education,city,'Male' as Gender
FROM #TAB M
CROSS APPLY
(
    SELECT * FROM MASTER..SPT_VALUES WHERE TYPE = 'P' AND NUMBER BETWEEN 1 AND M.MALE
)MALE

UNION ALL

SELECT F.age,education,city,'Female' as Gender
FROM #TAB F
CROSS APPLY
(
    SELECT * FROM MASTER..SPT_VALUES WHERE TYPE = 'P' AND NUMBER BETWEEN 1 AND F.FEMALE
)MALE

score 0 · Answer 3 · answered Jan 29 '19 at 09:50

UNPIVOT and a numbers table gets you there. I've simulated a very basic numbers table with just two rows here to make the query self contained:

declare @t table (age int,education varchar(15),city char(1),male int,female int)
insert into @t(age , education , city , male , female) values
(28,'Secondary','x',0,2),
(28,'University','x',1,2),
(29,'Primary','y',1,0)

;With UP as (
    select
        *
    from
        @t
            unpivot (cnt  for gender in (male,female)) p
), Numbers(n) as (
    select 1 union all select 2
)
select *
from UP inner join Numbers on n <= cnt

Results (with a couple of extra columns you can easily remove in the final SELECT):

age         education       city cnt         gender     n
----------- --------------- ---- ----------- ---------- -----------
28          Secondary       x    2           female     1
28          Secondary       x    2           female     2
28          University      x    1           male       1
28          University      x    2           female     1
28          University      x    2           female     2
29          Primary         y    1           male       1

(If you have a real numbers table and it includes 0 or negative numbers you'll want an additional filter in the join)

score -1 · Answer 4 · answered Jan 29 '19 at 09:52

You should use a store procedure to loop on the first table and insert records on the other:

DECLARE @iterator INT
DECLARE @male int
DECLARE @female int
DECLARE @age int
DECLARE @education varchar(50)
DECLARE city varchar(50)
DECLARE cur CURSOR LOCAL for
    SELECT male, female, age, city FROM table1
OPEN cur

FETCH NEXT FROM cur INTO @male, @female, @age, @city

WHILE @@FETCH_STATUS = 0 
BEGIN
  SET @Iterator = 0
  WHILE (@Iterator < @male)
  BEGIN
      INSERT INTO table2 VALUES (@age,@education,@city,'male')
      Set @Iterator = @Iterator + 1
  END 
  SET @Iterator = 0
  WHILE (@Iterator < @female)
  BEGIN
      INSERT INTO table2 VALUES (@age,@education,@city,'female')
      Set @Iterator = @Iterator + 1
  END 

    FETCH NEXT FROM cur INTO @male, @female, @age, @city
END

CLOSE cur
DEALLOCATE cur

Breakdown (un-group by) multiple column in SQL

4 Answers4