How to transform records data into columns

Question

Please find the sample data:

h_company_id    company_nm      mainphone1      phone_cnt
20816          800 Flowers      5162377000         3
20816          800 Flowers      5162377131         1
20820         1st Source Corp.  5742353000         3
20821       1st United Bancorp  5613633400         2
20824         3D Systems Inc.   8033273900         4
20824         3D Systems Inc.   8033464010         1
11043         3I Group PLC      2079757115         1
11043         3I Group PLC      2079753731         15

Desired Output:

h_company_id    company_nm          mainphone1    phone_cnt  mainphone2    phone_cnt2
20816        800 Flowers    5162377000  3        5162377131        1
20820        1st Source Corp.   5742353000  3           NULL          NULL
20821        1st United Bancorp 5613633400  2           NULL          NULL
20824        3D Systems Inc.    8033273900  4        8033464010        1
11043        3I Group PLC   2079757115  1        2079753731        15

(copy above in notepad/excel)

Hi Guys,

I want to transpose records of columns mainphone1 and phone_cnt as new columns namely mainphone2, phone_cnt2 so that the data in column h_company_id should be unique means there should be only single entry of h_company_id.

Thanks in advance!

Answer 1

Transforming from rows into columns is called a PIVOT and there are several different ways that this can be done in SQL Server.

Aggregate / CASE: You can use an aggregate function along with a CASE expression. This will work by applying the row_number() windowing function to the data in your table:

select h_company_id, company_nm,
  max(case when seq = 1 then mainphone1 end) mainphone1,
  max(case when seq = 1 then phone_cnt end) phone_cnt1,
  max(case when seq = 2 then mainphone1 end) mainphone2,
  max(case when seq = 2 then phone_cnt end) phone_cnt2
from
(
  select h_company_id, company_nm, mainphone1, phone_cnt,
    row_number() over(partition by h_company_id order by mainphone1) seq
  from yourtable
) d
group by h_company_id, company_nm;

See SQL Fiddle with Demo. The CASE expression checks if the sequence number has the value 1 or 2 and then places the data in the column.

UNPIVOT / PIVOT: Since you want to PIVOT data that exists in two columns, then you will want to UNPIVOT the mainphone1 and phone_cnt columns first to get them in the same column, then apply the PIVOT function.

The UNPIVOT code will be similar to the following:

select h_company_id, company_nm,
  col+cast(seq as varchar(10)) col,
  value
from 
(
  select h_company_id, company_nm, 
    cast(mainphone1 as varchar(15)) mainphone,
    cast(phone_cnt as varchar(15)) phone_cnt,
    row_number() over(partition by h_company_id order by mainphone1) seq
  from yourtable
) d
unpivot
(
  value
  for col in (mainphone, phone_cnt)
) unpiv;

See Demo. This query gets the data in the following format:

| H_COMPANY_ID |         COMPANY_NM |        COL |      VALUE |
---------------------------------------------------------------
|        11043 |       3I Group PLC | mainphone1 | 2079753731 |
|        11043 |       3I Group PLC | phone_cnt1 |         15 |
|        11043 |       3I Group PLC | mainphone2 | 2079757115 |
|        11043 |       3I Group PLC | phone_cnt2 |          1 |
|        20816 |        800 Flowers | mainphone1 | 5162377000 |

Then you apply the PIVOT function to the values in col:

select h_company_id, company_nm, 
  mainphone1, phone_cnt1, mainphone2, phone_cnt2
from 
(
  select h_company_id, company_nm,
    col+cast(seq as varchar(10)) col,
    value
  from 
  (
    select h_company_id, company_nm, 
      cast(mainphone1 as varchar(15)) mainphone,
      cast(phone_cnt as varchar(15)) phone_cnt,
      row_number() over(partition by h_company_id order by mainphone1) seq
    from yourtable
  ) d
  unpivot
  (
    value
    for col in (mainphone, phone_cnt)
  ) unpiv
) src
pivot
(
  max(value)
  for col in (mainphone1, phone_cnt1, mainphone2, phone_cnt2)
) piv;

See SQL Fiddle with Demo.

Multiple Joins: You can also join on your table multiple times to get the result.

;with cte as
(
  select h_company_id, company_nm, mainphone1, phone_cnt,
    row_number() over(partition by h_company_id order by mainphone1) seq
  from yourtable
)
select c1.h_company_id,
  c1.company_nm,
  c1.mainphone1,
  c1.phone_cnt phone_cnt1,
  c2.mainphone1 mainphone2,
  c2.phone_cnt phone_cnt2
from cte c1
left join cte c2
  on c1.h_company_id = c2.h_company_id
  and c2.seq = 2
where c1.seq = 1;

See SQL Fiddle with Demo.

Dynamic SQL: Finally if you have an unknown number of values that you want to transform, then you will need to implement dynamic SQL to get the result:

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

select @cols = STUFF((SELECT ',' + QUOTENAME(col+cast(seq as varchar(10))) 
                    from
                    (
                      select row_number() over(partition by h_company_id order by mainphone1) seq
                      from yourtable
                    ) d
                    cross apply
                    (
                      select 'mainphone', 1 union all
                      select 'phone_cnt', 2
                    ) c (col, so)
                    group by seq, so, col
                    order by seq, so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = 'SELECT h_company_id, company_nm,' + @cols + ' 
              from 
             (
                select h_company_id, company_nm,
                  col+cast(seq as varchar(10)) col,
                  value
                from 
                (
                  select h_company_id, company_nm, 
                    cast(mainphone1 as varchar(15)) mainphone,
                    cast(phone_cnt as varchar(15)) phone_cnt,
                    row_number() over(partition by h_company_id order by mainphone1) seq
                  from yourtable
                ) d
                unpivot
                (
                  value
                  for col in (mainphone, phone_cnt)
                ) unpiv
            ) x
            pivot 
            (
                max(value)
                for col in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo. All give a result:

| H_COMPANY_ID |         COMPANY_NM | MAINPHONE1 | PHONE_CNT1 | MAINPHONE2 | PHONE_CNT2 |
-----------------------------------------------------------------------------------------
|        20820 |   1st Source Corp. | 5742353000 |          3 |     (null) |     (null) |
|        20821 | 1st United Bancorp | 5613633400 |          2 |     (null) |     (null) |
|        20824 |    3D Systems Inc. | 8033273900 |          4 | 8033464010 |          1 |
|        11043 |       3I Group PLC | 2079753731 |         15 | 2079757115 |          1 |
|        20816 |        800 Flowers | 5162377000 |          3 | 5162377131 |          1 |

Answer 2

Try this query. This will help you

SELECT t.H_COMPANY_ID,t.COMPANY_NM, a.mainphone1,a.PHONE_CNT,b.mainphone1 mainphone2,b.PHONE_CNT PHONE_CNT2 FROM table_name t
INNER JOIN
(
   SELECT h_company_id,phone_cnt,mainphone1 FROM table_name
   WHERE mainphone1 
   IN(
       SELECT max(mainphone1) mainphone1 FROM table_name GROUP BY h_company_id
     )
)a ON t.H_COMPANY_ID = a.h_company_id
INNER JOIN
(
   SELECT h_company_id,phone_cnt,mainphone1 FROM table_name
   WHERE mainphone1 
   IN(
       SELECT min(mainphone1) mainphone1 from table_name GROUP BY h_company_id
     )
)b ON t.H_COMPANY_ID = b.H_COMPANY_ID 
GROUP BY t.H_COMPANY_ID,a.mainphone1,t.COMPANY_NM,a.PHONE_CNT,b.mainphone1,b.PHONE_CNT 

Answer 3

The following could work (assuming your table is called company):

SELECT
    c1.h_company_id,
    c1.company_nm,
    c1.mainphone1,
    c1.phone_cnt,
    c2.mainphone1 AS mainphone2,
    c2.phone_cnt AS phone_cnt2
FROM
    company AS c1
LEFT JOIN
    company AS c2 ON c2.h_company_id = c1.h_company_id

However, to respect good practice, wouldn't it be better to separate your data in two tables?

• the company table, with 2 columns: h_company_id(PK) and company_nm
• the phone table, with 4 columns: phone_id (PK), h_company_id (FK), mainphone and phone_cnt

It would allow you to have as many phone numbers per company as you want (including none).