Converting rows records to columns in mySQL

Question

I'm trying to convert row data to columns. Data will be extracted from different tables. I tried using PIVOTbut I'm not much successful.

Lets consider column#1 as primary key in every table.

CREATE TABLE Table_pivot_01
    ([SSN ID] int, [Citizen_name] varchar(5), [Company] varchar(4))
;

INSERT INTO Table_pivot_01
    ([SSN ID], [Citizen_name], [Company])
VALUES
    (12345, 'John', 'XYZ'),
    (12346, 'Tom', 'ABC'),
    (12347, 'Jerry', 'QWER'),
    (12348, 'Joe', 'PQR'),
    (12349, 'Josh', NULL)
;

CREATE TABLE Table_pivot_02
    ([Serial] int, [SSN_ID] int, [Family_details] varchar(9), [Family_members_name] varchar(10))
;

INSERT INTO Table_pivot_02
    ([Serial], [SSN_ID], [Family_details], [Family_members_name])
VALUES
    (1010, 12345, 'Spouse', 'Mari'),
    (1011, 12345, 'Child - 1', 'John Jr. 1'),
    (1012, 12345, 'Child - 2', 'John Jr. 2'),
    (1013, 12346, 'Spouse', 'Ken'),
    (1014, 12347, 'Spouse', 'Suzen'),
    (1015, 12347, 'Child - 1', 'Jerry Jr.1'),
    (1016, 12347, 'Child - 2', 'Jerry Jr.2'),
    (1017, 12347, 'Child - 3', 'Jerry Jr.3'),
    (1018, 12348, 'Child - 1', 'Joe Jr.1'),
    (1019, 12348, 'Child - 2', 'Joe Jr.2'),
    (1020, 12349, 'Spouse', 'Zoe'),
    (1021, 12349, 'Child - 1', 'Josh Jr.1'),
    (1022, 12349, 'Child - 2', 'Josh Jr.2')
;



CREATE TABLE Table_pivot_03
    ([Row] int, [SSN_ID] int, [Address_type] varchar(8), [Address] varchar(22), [PhoneNumber_type] varchar(6), [PhoneNumber] varchar(18))
;

INSERT INTO Table_pivot_03
    ([Row], [SSN_ID], [Address_type], [Address], [PhoneNumber_type], [PhoneNumber])
VALUES
    (121, 12345, 'Present', 'Address_John_Present', 'Home', 'John_Home_phone'),
    (122, 12345, 'Office', 'Address_John_Office', 'Office', 'John_Office_phone'),
    (123, 12345, 'Perement', 'Address_John_Perement', 'Fax', 'John_FAX_phone'),
    (124, 12346, 'Present', 'Address_Tom_Present', 'Home', 'Tom_Home_phone'),
    (125, 12346, 'Office', 'Address_Tom_Office', 'Office', 'Tom_Office_phone'),
    (126, 12347, 'Office', 'Address_Jerry_Office', 'Home', 'Jerry_Home_phone'),
    (127, 12347, 'Perement', 'Address_Jerry_Perement', 'Office', 'Jerry_Office_phone'),
    (128, 12348, 'Present', 'Address_Joe_Present', 'Home', 'Joe_Home_phone'),
    (129, 12348, 'Office', 'Address_Joe_Office', 'Office','Joe_Office_phone'),
    (130, 12348, 'Perement' , 'Address_Josh_Perement','','' ),
     (131, 12349, 'Present','Address_Josh_Present','Home','Josh_Home_phone'),
     (132, 12349, 'Perement', 'Address_Josh_Perement' , 'Fax' ,'Josh_FAX_phone');

Table schema : http://rextester.com/MSXK16689

The Expected Output is:

How can I build the result in effective way?

Answer 1

MySQL Version

You've stated that you have tried using PIVOT but MySQL doesn't have a PIVOT function. In MySQL you need to use an aggregate function along with a conditional logic statement like CASE...WHEN or something similar. You also have several tables and several different columns you need to pivot which complicates this a bit. It also seems that you have an unknown number of new columns that need to be created, which adds another layer of complexity.

If you know all of the columns you want to be displayed in the final result, then you can easily type up a version of this query to be something like:

select 
    p1.`SSN_ID`,
    p1.Citizen_name,
    p1.Company,
    max(case when p2.Family_details = 'Spouse' then Family_members_name end) Spouse,
    max(case when p2.Family_details = 'Child - 1' then Family_members_name end) Child1,
    max(case when p2.Family_details = 'Child - 2' then Family_members_name end) Child2,
    max(case when p2.Family_details = 'Child - 3' then Family_members_name end) Child3,
    max(case when p2.Family_details = 'Child - 4' then Family_members_name end) Child4,
    max(case when p3.Address_type = 'Present' then p3.Address end) PresentAddress,
    max(case when p3.Address_type = 'Office' then p3.Address end) OfficeAddress,
    max(case when p3.Address_type = 'Perement' then p3.Address end) PermAddress,
    max(case when p3.PhoneNumber_type = 'Home' then p3.PhoneNumber end) HomePhone,
    max(case when p3.PhoneNumber_type = 'Office' then p3.PhoneNumber end) OfficePhone,
    max(case when p3.PhoneNumber_type = 'Fax' then p3.PhoneNumber end) FaxPhone
from Table_pivot_01 p1
left join Table_pivot_02 p2
    on p1.`SSN_ID` = p2.`SSN_ID`
left join Table_pivot_03 p3
    on p1.`SSN_ID` = p3.`SSN_ID`
group by p1.`SSN_ID`,
    p1.Citizen_name,
    p1.Company;

Basically you create a new column in a max(case... statement and it will display the value. As mentioned, this gets a bit more complicated if you have unknown values you want as columns. In MySQL you need to use a Prepared Statement so you can use dynamic SQL. Your code would looks sort of like this:

SET @sql = NULL;   
SET @sql1 = NULL;
SET @sql2 = NULL;
SET @sql3 = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      ' max(case when p2.Family_details = ''',
      Family_details,
      ''' then Family_members_name end) AS `',
      Family_details, '`'
    )
  ) INTO @sql1
FROM  Table_pivot_02;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      ' max(case when p3.Address_type = ''',
      Address_type,
      ''' then Address end) AS `',
      Address_type, '`'
    )
  ) INTO @sql2
FROM  Table_pivot_03;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      ' max(case when p3.PhoneNumber_type = ''',
      PhoneNumber_type,
      ''' then PhoneNumber end) AS `',
      PhoneNumber_type, '`'
    )
  ) INTO @sql3
FROM  Table_pivot_03
where PhoneNumber_type <> '';

SET @sql = CONCAT('SELECT p1.`SSN_ID`,
                        p1.Citizen_name,
                        p1.Company, ', @sql1, ',', @sql2, ',', @sql3, ' 
                    from Table_pivot_01 p1
                    left join Table_pivot_02 p2
                        on p1.`SSN_ID` = p2.`SSN_ID`
                    left join Table_pivot_03 p3
                        on p1.`SSN_ID` = p3.`SSN_ID`
                    group by p1.`SSN_ID`,
                        p1.Citizen_name,
                        p1.Company');


PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

In this you are creating a long string of the max(case... statements that get concatenated together to then be executed by the database engine. There may be easier ways to get the result you want, but this does work. I've created a demo on rextester to show the code. Both of these produce a result:

+-------+--------+--------------+---------+--------+------------+------------+------------+----------------------+----------------------+------------------------+------------------+--------------------+----------------+
|  Row  | SSN_ID | Citizen_name | Company | Spouse | Child - 1  | Child - 2  | Child - 3  |       Present        |        Office        |        Perement        |       Home       |       Office       |      Fax       |
+-------+--------+--------------+---------+--------+------------+------------+------------+----------------------+----------------------+------------------------+------------------+--------------------+----------------+
|     1 |  12345 | John         | XYZ     | Mari   | John Jr. 1 | John Jr. 2 | NULL       | Address_John_Present | Address_John_Office  | Address_John_Perement  | John_Home_phone  | John_Office_phone  | John_FAX_phone |
|     2 |  12346 | Tom          | ABC     | Ken    | NULL       | NULL       | NULL       | Address_Tom_Present  | Address_Tom_Office   | NULL                   | Tom_Home_phone   | Tom_Office_phone   | NULL           |
|     3 |  12347 | Jerry        | QWER    | Suzen  | Jerry Jr.1 | Jerry Jr.2 | Jerry Jr.3 | NULL                 | Address_Jerry_Office | Address_Jerry_Perement | Jerry_Home_phone | Jerry_Office_phone | NULL           |
|     4 |  12348 | Joe          | PQR     | NULL   | Joe Jr.1   | Joe Jr.2   | NULL       | Address_Joe_Present  | Address_Joe_Office   | Address_Josh_Perement  | Joe_Home_phone   | Joe_Office_phone   | NULL           |
|     5 |  12349 | Josh         | NULL    | Zoe    | Josh Jr.1  | Josh Jr.2  | NULL       | Address_Josh_Present | NULL                 | Address_Josh_Perement  | Josh_Home_phone  | NULL               | Josh_FAX_phone |
+-------+--------+--------------+---------+--------+------------+------------+------------+----------------------+----------------------+------------------------+------------------+--------------------+----------------+

Based on your comment that you might have more than one phone number type per person, you'll need to create a row number for each group of phone types. Unfortunately, again MySQL doesn't have windowing function so you'll need to use user defined variables to get the final result. When you query for PhoneNumber_type you'll need to use something like:

select *
from 
(
   select SSN_ID, PhoneNumber_type, PhoneNumber,
      @num:= case when @group = SSN_ID and @type = PhoneNumber_type then @num +1 else if(@group := SSN_ID, 1, 1) end rn,
      @group:= SSN_ID,
      @type:=PhoneNumber_type
  from Table_pivot_03 t
  CROSS JOIN (select @num:=0, @group:=null, @type:=null) c
  where t.PhoneNumber_type <> ''
  order by SSN_ID, PhoneNumber_type
) as x;     

This creates a row number value for each user and phone type. You'd then integrate this into the dynamic SQL code:

SET @sql = NULL;   
SET @sql1 = NULL;
SET @sql2 = NULL;
SET @sql3 = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      ' max(case when p2.Family_details = ''',
      Family_details,
      ''' then Family_members_name end) AS `',
      Family_details, '`'
    )
  ) INTO @sql1
FROM  Table_pivot_02;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      ' max(case when p3.Address_type = ''',
      Address_type,
      ''' then Address end) AS `',
      Address_type, '`'
    )
  ) INTO @sql2
FROM  Table_pivot_03;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      ' max(case when p.PhoneNumber_type = ''',
      PhoneNumber_type,
      ''' and rn = ', rn, ' then p.PhoneNumber end) AS `',
      PhoneNumber_type, rn, '`'
    )
  ) INTO @sql3
FROM 
(
   select SSN_ID, PhoneNumber_type, PhoneNumber,
      @num:= case when @group = SSN_ID and @type = PhoneNumber_type then @num +1 else if(@group := SSN_ID, 1, 1) end rn,
      @group:= SSN_ID,
      @type:=PhoneNumber_type
  from Table_pivot_03 t
  CROSS JOIN (select @num:=0, @group:=null, @type:=null) c
  where t.PhoneNumber_type <> ''
  order by SSN_ID, PhoneNumber_type
) as x;


SET @sql = CONCAT('SELECT p1.`SSN_ID`,
                        p1.Citizen_name,
                        p1.Company, ', @sql1, ',', @sql2, ',', @sql3, ' 
                    from Table_pivot_01 p1
                    left join Table_pivot_02 p2
                        on p1.`SSN_ID` = p2.`SSN_ID`
                    left join Table_pivot_03 p3
                      on p1.SSN_ID = p3.SSN_Id
                    left join
                    (
                       select SSN_ID, PhoneNumber_type, PhoneNumber,
                          @num:= case when @group = SSN_ID and @type = PhoneNumber_type then @num +1 else if(@group := SSN_ID, 1, 1) end rn,
                          @group:= SSN_ID,
                          @type:=PhoneNumber_type
                      from Table_pivot_03 t
                      CROSS JOIN (select @num:=0, @group:=null, @type:=null) c
                      where t.PhoneNumber_type <> ''''
                      order by SSN_ID, PhoneNumber_type
                    ) as p
                       on p1.SSN_ID = p.SSN_Id
                    group by p1.`SSN_ID`,
                        p1.Citizen_name,
                        p1.Company');

#select @sql;                        

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See another demo.

---

SQL Server Version

Since you've now said that you need a SQL Server version here is that version. SQL Server has a few features that make doing this significantly easier including a PIVOT function, UNPIVOT function, and windowing functions like row_number. Here's a static version of the query with just a few columns pivoted:

select SSN_ID,
    Citizen_name,
    Company,
    Spouse, [Child - 1], [Child - 2], [Child - 3], [Child - 4]
from
(
    select SSN_ID,
        Citizen_name,
        Company, 
        col, 
        value
    from
    (
        select 
            p1.SSN_ID,
            p1.Citizen_name,
            p1.Company,
            p2.Family_details,
            p2.Family_members_name,
            p3.Address_type,
            p3.Address,
            PhoneNumber_type = p.PhoneNumber_type + cast(p.rn as varchar(10)),
            p.PhoneNumber
        from Table_pivot_01 p1
        left join Table_pivot_02 p2
            on p1.SSN_ID = p2.SSN_ID
        left join Table_pivot_03 p3
            on p1.SSN_ID = p3.SSN_ID
        left join
        (
            select SSN_ID, PhoneNumber_type, PhoneNumber,
                rn = row_number() over(partition by SSN_ID, PhoneNumber_type order by SSN_ID, PhoneNumber_type)
            from Table_pivot_03
            where PhoneNumber_type <> ''
        ) p
            on p1.SSN_ID = p.SSN_ID
    ) d
    cross apply 
    (
        select 'Family_details', Family_details, Family_members_name union all
        select 'Address_type', Address_type, Address union all
        select 'PhoneNumber_type', PhoneNumber_type, PhoneNumber
    ) c(orig, col, value)
) src
pivot
(
    max(value)
    for col in (Spouse, [Child - 1], [Child - 2], [Child - 3], [Child - 4])
) piv

Then if you need a dynamic sql version the code would be like:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(Col) 
                    from
                    (
                        select col, ord
                        from
                        (
                            select 
                                p1.SSN_ID,
                                p1.Citizen_name,
                                p1.Company,
                                p2.Family_details,
                                p2.Family_members_name,
                                p3.Address_type,
                                p3.Address,
                                PhoneNumber_type = p.PhoneNumber_type + cast(p.rn as varchar(10)),
                                p.PhoneNumber
                            from Table_pivot_01 p1
                            left join Table_pivot_02 p2
                                on p1.SSN_ID = p2.SSN_ID
                            left join Table_pivot_03 p3
                                on p1.SSN_ID = p3.SSN_ID
                            left join
                            (
                                select SSN_ID, PhoneNumber_type, PhoneNumber,
                                    rn = row_number() over(partition by SSN_ID, PhoneNumber_type order by SSN_ID, PhoneNumber_type)
                                from Table_pivot_03
                                where PhoneNumber_type <> ''
                            ) p
                                on p1.SSN_ID = p.SSN_ID
                        ) d
                        cross apply 
                        (
                            select 'Family_details', Family_details, Family_members_name, 1 union all
                            select 'Address_type', Address_type, Address, 2 union all
                            select 'PhoneNumber_type', PhoneNumber_type, PhoneNumber, 3
                        ) c(orig, col, value, ord)
                    ) d
                    group by col, ord
                    order by ord, col
                    FOR XML PATH(''), TYPE
                    ).value('.', 'NVARCHAR(MAX)') ,1,1,'');


set @query = N'SELECT ' + @cols + N' from 
            (
                select SSN_ID,
                    Citizen_name,
                    Company, 
                    col, 
                    value
                from
                (
                    select 
                        p1.SSN_ID,
                        p1.Citizen_name,
                        p1.Company,
                        p2.Family_details,
                        p2.Family_members_name,
                        p3.Address_type,
                        p3.Address,
                        PhoneNumber_type = p.PhoneNumber_type + cast(p.rn as varchar(10)),
                        p.PhoneNumber
                    from Table_pivot_01 p1
                    left join Table_pivot_02 p2
                        on p1.SSN_ID = p2.SSN_ID
                    left join Table_pivot_03 p3
                        on p1.SSN_ID = p3.SSN_ID
                    left join
                    (
                        select SSN_ID, PhoneNumber_type, PhoneNumber,
                            rn = row_number() over(partition by SSN_ID, PhoneNumber_type order by SSN_ID, PhoneNumber_type)
                        from Table_pivot_03
                        where PhoneNumber_type <> ''''
                    ) p
                        on p1.SSN_ID = p.SSN_ID
                ) d
                cross apply 
                (
                    select ''Family_details'', Family_details, Family_members_name union all
                    select ''Address_type'', Address_type, Address union all
                    select ''PhoneNumber_type'', PhoneNumber_type, PhoneNumber
                ) c(orig, col, value)
            ) src
            pivot
            (
                max(value)
                for col in (' + @cols + N')
            ) p '

exec sp_executesql @query;          

Here is another demo.