unpivot issue with multiple like columns

Question

I have a CSV import file that I have no control over concerning the column names or format. The headers consist of id,[temperature range 1],[temperature range 2],[temperature range 3],[temperature range],[temperature range 5],[temperature range 6],[temperature range 7],[temperature range 8], [temperature range 9],[temperature range 10],[temperature range 11],[temperature range 12], [Pressure Range 1],[Pressure Range 2],[Pressure Range 3],[Pressure Range 4],[Pressure Range 5], [Pressure Range 6],[Pressure Range 7],[Pressure Range 8],[Pressure Range 9], [Pressure Range 10],[Pressure Range 11],[Pressure Range 12],[Calcite Saturation 1],[Calcite Saturation 2],[Calcite Saturation 3],[Calcite Saturation 4],[Calcite Saturation 5],[Calcite Saturation 6],[Calcite Saturation 7],[Calcite Saturation 8],[Calcite Saturation 9],[Calcite Saturation 10],[Calcite Saturation 11],[Calcite Saturation 12].

I have been able to unpivot the table using this code:

Select id,Temp,Psia,CalciteX
from (
    Select id,[temperature range 1],[temperature range 2],[temperature range 3],[temperature range 4],[temperature range 5],[temperature range 6],[temperature range 7],[temperature range 8], [temperature range 9],[temperature range 10],[temperature range 11],[temperature range 12],[Pressure Range 1],[Pressure Range 2],[Pressure Range 3],[Pressure Range 4],[Pressure Range 5], [Pressure Range 6],[Pressure Range 7],[Pressure Range 8],[Pressure Range 9],[Pressure Range 10],[Pressure Range 11],[Pressure Range 12],[Calcite Saturation 1],[Calcite Saturation 2], [Calcite Saturation 3],[Calcite Saturation 4],[Calcite Saturation 5],[Calcite Saturation 6],[Calcite Saturation 7],[Calcite Saturation 8],[Calcite Saturation 9],[Calcite Saturation 10], [Calcite Saturation 11],[Calcite Saturation 12]
    from frenchcreekscale where id ='10009-2019111114.1/P' ) as src

UNPivot ( Temp for Temps in([temperature range 1],[temperature range 2],[temperature range 3],[temperature range 4],[temperature range 5],[temperature range 6],[temperature range 7],[temperature range 8], [temperature range 9],[temperature range 10],[temperature range 11],[temperature range 12]) ) AS Temps
 
UNPivot ( Psia for Pressures in([Pressure Range 1],[Pressure Range 2],[Pressure Range 3],[Pressure Range 4],[Pressure Range 5],[Pressure Range 6],[Pressure Range 7],[Pressure Range 8],[Pressure Range 9], [Pressure Range 10],[Pressure Range 11],[Pressure Range 12]) ) AS Pressures

UNPivot ( CalciteX for CalciteXs in([Calcite Saturation 1],[Calcite Saturation 2], [Calcite Saturation 3],[Calcite Saturation 4],[Calcite Saturation 5],[Calcite Saturation 6],[Calcite Saturation 7],[Calcite Saturation 8],[Calcite Saturation 9],[Calcite Saturation 10], [Calcite Saturation 11],[Calcite Saturation 12]) ) AS CalX

This is the partial output I am getting:

id                   Temp Psia CalciteX
-------------------- ---- ---- --------
10009-2019111114.1/P 70   0    0.165885
10009-2019111114.1/P 70   0    0.180097
10009-2019111114.1/P 70   0    0.195601
10009-2019111114.1/P 70   0    0.211319
10009-2019111114.1/P 70   0    0.226902
10009-2019111114.1/P 70   0    0.241826
10009-2019111114.1/P 70   0    0.25538
10009-2019111114.1/P 70   0    0.267159
10009-2019111114.1/P 70   0    0.276571
10009-2019111114.1/P 70   0    0.283237
10009-2019111114.1/P 70   0    0.286532
10009-2019111114.1/P 70   0    0.286462
10009-2019111114.1/P 70   147  0.165885
10009-2019111114.1/P 70   147  0.180097
10009-2019111114.1/P 70   147  0.195601
10009-2019111114.1/P 70   147  0.211319
10009-2019111114.1/P 70   147  0.226902
10009-2019111114.1/P 70   147  0.241826
10009-2019111114.1/P 70   147  0.25538
10009-2019111114.1/P 70   147  0.267159
10009-2019111114.1/P 70   147  0.276571
10009-2019111114.1/P 70   147  0.283237
10009-2019111114.1/P 70   147  0.286532
10009-2019111114.1/P 70   147  0.286462
10009-2019111114.1/P 70   278  0.165885
10009-2019111114.1/P 70   278  0.180097
etc

It should only be 12 records:

id                   Temp 
-------------------- ----
10009-2019111114.1/P 70
10009-2019111114.1/P 80
10009-2019111114.1/P 90
10009-2019111114.1/P 100
10009-2019111114.1/P 110
10009-2019111114.1/P 120
10009-2019111114.1/P 130
10009-2019111114.1/P 140
10009-2019111114.1/P 150
10009-2019111114.1/P 160
10009-2019111114.1/P 170
10009-2019111114.1/P 180

With the other fields tied to the id.

I am not understanding how to unpivot the next column with out generating the multiple records.

Sander · Answer 1 · 2020-09-20T10:20:36.127

pivot and unpivot work best when you only provide them the columns needed for the pivot. Your src subset contains columns for 3 unpivot's. If you split those up in separate common table expressions (CTE's), you can then join them back on the id column.

Please note that joining on id alone will not be enough. This will still give you the multiple rows. You want the value for [temperature range 1] on the same line as [Pressure Range 1] and [Pressure Range 1]. So you must add that to your join criteria.

In my simplified example I used the single rightmost charater from the column name to construct this extra join criterium (giving me 1 and 2). Your full solution should the last two numbers from the column names (giving you 1 to 12).

Simplified sample data

create table MyTable
(
  id int,
  A1 nvarchar(4),
  A2 nvarchar(4),
  B1 nvarchar(4),
  B2 nvarchar(4)
);

insert into MyTable (id, A1, A2, B1, B2) values
(1, '1A11', '1A21', '1B11', '1B21'),
(2, '2A11', '2A21', '2B11', '2B12');

Simplified solution

with cte_A as
(
  select upA.id, upA.AValue, upA.AType
  from (select id, A1, A2 from MyTable) a
  unpivot (AValue for AType in ([A1], [A2])) upA
),
cte_B as
(
  select upB.id, upB.BValue, upB.BType
  from (select id, B1, B2 from MyTable) b
  unpivot (BValue for BType in ([B1], [B2])) upB
)
select ca.id, right(ca.AType, 1) as Num, ca.AValue, ca.AType, cb.BValue, cb.BType
from cte_A ca
join cte_B cb
  on  cb.id = ca.id                            -- match on id
  and right(cb.BType, 1) = right(ca.AType, 1); -- match on type (bring A1 and B1 to same line)

Sample output

id Num AValue AType BValue BType
-- --- ------ ----- ------ -----
1  1   1A11   A1    1B11   B1
1  2   1A21   A2    1B21   B2
2  1   2A11   A1    2B11   B1
2  2   2A21   A2    2B12   B2

Fiddle

Existing query

Looking back on my solution: you can also apply the "extra join criterium" to your existing query. Add something like the following:

... 
where right(CalX.Temps, 2) = right(CalX.Pressures, 2)
  and right(CalX.Temps, 2) = right(CalX.CalciteXs, 2);

Fiddle that applies this solution to a simplified version of your query.

unpivot issue with multiple like columns

1 Answers1