CODEWITHSUNDEEP
sqlsql-serverpivot
Mastor
Mastor

Reputation: 189

SQL combine 2 table and pivot

I don't understand how PIVOT works in SQL. I have 2 tables and I would like to pivot 1 of them in order to get only 1 table with all the data together. I've attached an image with the tables I have and the result that I would like to get.

enter image description here

CREATE TABLE TABLE1
    ([serie_id] varchar(4), [Maturity] int, [Strategy] int, [Lifetime] varchar(4), [L_max] decimal(10, 5), [W_max] decimal(10, 5), [H_max] decimal(10, 5))
;

INSERT INTO TABLE1
    ([serie_id], [Maturity], [Strategy], [Lifetime], [L_max], [W_max], [H_max])
VALUES
    ('id_1', 3, 1, '2', 2.200, 1.400, 1.400),
    ('id_2', 3, 1, '2', 3.400, 1.800, 2.100),
    ('id_3', 3, 1, NULL, 24.500, 14.500, 15.000),
    ('id_4', 3, 1, NULL, 28.000, 24.500, 14.000)
;

CREATE TABLE TABLE2
    ([serie_id] varchar(4), [L_value] decimal(10, 5), [lrms] decimal(10, 5), [latTmax] decimal(10, 5), [Rdc] decimal(10, 5))
;

INSERT INTO TABLE2
    ([serie_id], [L_value], [lrms], [latTmax], [Rdc])
VALUES
    ('id_1', 67.000, 400.000, 400.000, 0.250),
    ('id_1', 90.000, 330.000, 330.000, 0.350),
    ('id_1', 120.000, 370.000, 370.000, 0.300),
    ('id_1', 180.000, 330.000, 300.000, 0.350),
    ('id_2', 260.000, 300.000, 300.000, 0.400),
    ('id_2', 360.000, 280.000, 280.000, 0.450),
    ('id_3', 90.000, 370.000, 370.000, 0.300),
    ('id_4', 160.000, 340.000, 340.000, 0.400)
;

SQLFiddle

If someone could help me with the SQL query I would appreciate it so much.

Upvotes: 2

Views: 1819

Answers (1)

Taryn
Taryn

Reputation: 247810

In order to get your final result, you are going to have to implement a variety of methods including unpivot, pivot, along with the use of a windowing function like row_number().

Since you have multiple columns in Table2 that need to be pivoted, then you will need to unpivot them first. This is the reverse of pivot, which converts your multiple columns into multiple rows. But before you unpivot, you need some value to identify the values of each row using row_number() - sounds complicated, right?

First, query table2 using the windowing function row_number(). This creates a unique identifier for each row and allows you to easily be able to associate the values for id_1 from any of the others. 

select serie_id, l_value, lrms, latTmax, Rdc,
    rn = cast(row_number() over(partition by serie_id order by serie_id)
              as varchar(10))
from table2;

See Demo. Once you've created this unique identifier, then you will unpivot the L_value, lrms, latTmax, and rdc. You can unpivot the data using several different methods, including the unpivot function, CROSS APPLY, or UNION ALL. 

select serie_id,
  col, value
from
(
  select serie_id, l_value, lrms, latTmax, Rdc,
    rn = cast(row_number() over(partition by serie_id order by serie_id)
              as varchar(10))
  from table2 
) d
cross apply 
(
  select 'L_value_'+rn, L_value union all
  select 'lrms_'+rn, lrms union all
  select 'latTmax_'+rn, latTmax union all
  select 'Rdc_'+rn, Rdc
) c (col, value)

See SQL Fiddle with Demo. The data from table2 is not in a completely different format that can be pivoted into the new columns:

| SERIE_ID |       COL | VALUE |
|----------|-----------|-------|
|     id_1 | L_value_1 |    67 |
|     id_1 |    lrms_1 |   400 |
|     id_1 | latTmax_1 |   400 |
|     id_1 |     Rdc_1 |  0.25 |
|     id_1 | L_value_2 |    90 |
|     id_1 |    lrms_2 |   330 |
|     id_1 | latTmax_2 |   330 |
|     id_1 |     Rdc_2 |  0.35 |

The final step would be to PIVOT the data above into the final result:

select serie_id, maturity, strategy, lifetime, l_max, w_max, h_max,
  L_value_1, lrms_1, latTmax_1, Rdc_1,
  L_value_2, lrms_2, latTmax_2, Rdc_2,
  L_value_3, lrms_3, latTmax_3, Rdc_3,
  L_value_4, lrms_4, latTmax_4, Rdc_4
from
(
  select t1.serie_id, t1.maturity, t1.strategy, t1.lifetime,
    t1.l_max, t1.w_max, t1.h_max,
    t2.col, t2.value
  from table1 t1
  inner join
  (
    select serie_id,
      col, value
    from
    (
      select serie_id, l_value, lrms, latTmax, Rdc,
        rn = cast(row_number() over(partition by serie_id order by serie_id)
                  as varchar(10))
      from table2 
    ) d
    cross apply 
    (
      select 'L_value_'+rn, L_value union all
      select 'lrms_'+rn, lrms union all
      select 'latTmax_'+rn, latTmax union all
      select 'Rdc_'+rn, Rdc
    ) c (col, value)
  ) t2
    on t1.serie_id = t2.serie_id
) d
pivot
(
  max(value)
  for col in (L_value_1, lrms_1, latTmax_1, Rdc_1,
              L_value_2, lrms_2, latTmax_2, Rdc_2,
              L_value_3, lrms_3, latTmax_3, Rdc_3,
              L_value_4, lrms_4, latTmax_4, Rdc_4)
) p;

See SQL Fiddle with Demo.

If you have an unknown number of values in Table2 then you will need to use dynamic SQL to create a sql string that will be executed. Converting the above code to dynamic sql is pretty easy once you have the logic correct. The code will be:

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

select @cols 
    = STUFF((SELECT ',' + QUOTENAME(col+cast(rn as varchar(10))) 
             from 
             (
               select rn = cast(row_number() over(partition by serie_id order by serie_id)
                  as varchar(10))
               from table2
             ) d
             cross apply
             (
               select 'L_value_', 0 union all
                select 'lrms_', 1 union all
                select 'latTmax_', 2 union all
                select 'Rdc_', 3
             ) c (col, so)
             group by col, rn, so
             order by rn, so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = N'SELECT serie_id, maturity, strategy, lifetime, l_max, 
                w_max, h_max,' + @cols + N' 
              from 
             (
               select t1.serie_id, t1.maturity, t1.strategy, t1.lifetime,
                t1.l_max, t1.w_max, t1.h_max,
                t2.col, t2.value
              from table1 t1
              inner join
              (
                select serie_id,
                  col, value
                from
                (
                  select serie_id, l_value, lrms, latTmax, Rdc,
                    rn = cast(row_number() over(partition by serie_id order by serie_id)
                              as varchar(10))
                  from table2 
                ) d
                cross apply 
                (
                  select ''L_value_''+rn, L_value union all
                  select ''lrms_''+rn, lrms union all
                  select ''latTmax_''+rn, latTmax union all
                  select ''Rdc_''+rn, Rdc
                ) c (col, value)
              ) t2
                on t1.serie_id = t2.serie_id
            ) x
            pivot 
            (
                max(value)
                for col in (' + @cols + N')
            ) p '

exec sp_executesql @query

See SQL Fiddle with Demo

Both versions will give a result of:

| SERIE_ID | MATURITY | STRATEGY | LIFETIME | L_MAX | W_MAX | H_MAX | L_VALUE_1 | LRMS_1 | LATTMAX_1 | RDC_1 | L_VALUE_2 | LRMS_2 | LATTMAX_2 |  RDC_2 | L_VALUE_3 | LRMS_3 | LATTMAX_3 |  RDC_3 | L_VALUE_4 | LRMS_4 | LATTMAX_4 |  RDC_4 |
|----------|----------|----------|----------|-------|-------|-------|-----------|--------|-----------|-------|-----------|--------|-----------|--------|-----------|--------|-----------|--------|-----------|--------|-----------|--------|
|     id_1 |        3 |        1 |        2 |   2.2 |   1.4 |   1.4 |        67 |    400 |       400 |  0.25 |        90 |    330 |       330 |   0.35 |       120 |    370 |       370 |    0.3 |       180 |    330 |       300 |   0.35 |
|     id_2 |        3 |        1 |        2 |   3.4 |   1.8 |   2.1 |       260 |    300 |       300 |   0.4 |       360 |    280 |       280 |   0.45 |    (null) | (null) |    (null) | (null) |    (null) | (null) |    (null) | (null) |
|     id_3 |        3 |        1 |   (null) |  24.5 |  14.5 |    15 |        90 |    370 |       370 |   0.3 |    (null) | (null) |    (null) | (null) |    (null) | (null) |    (null) | (null) |    (null) | (null) |    (null) | (null) |
|     id_4 |        3 |        1 |   (null) |    28 |  24.5 |    14 |       160 |    340 |       340 |   0.4 |    (null) | (null) |    (null) | (null) |    (null) | (null) |    (null) | (null) |    (null) | (null) |    (null) | (null) |

Upvotes: 2

Related Questions