CODEWITHSUNDEEP
sqlsql-server
Carl Zheng
Carl Zheng

Reputation: 728

SQL Server separate a column into some columns

The data looks like:

Measure   Value    Date  
--------------------------------
1         2.3      2016-01-01
1         2.5      2016-01-02
1         2.6      2016-01-03
2         1.2      2016-01-01
2         1.3      2016-01-02
2         1.4      2016-01-03

I want to make the Value column separated into 2 columns depending on the Measures

The final output should look like 

Date          Value_Measure_1     Value_Measure_2
---------------------------------------------------------------------
2016-01-01    2.3                 1.2
2016-01-02    2.5                 1.3
2016-01-03    2.6                 1.4

The only way I can find out is self-join.

Is there any other method could do it better?

Thank you!

Upvotes: 1

Views: 56

Answers (3)

Franck Ngako
Franck Ngako

Reputation: 157

I'll suggest you to use the pivot function with some T-SQL.

Upvotes: 0

sagi
sagi

Reputation: 40491

Use conditional aggregation :

SELECT t.date,
       MAX(CASE WHEN t.Measure = 1 THEN t.Value END) as val_meas_1,
       MAX(CASE WHEN t.Measure = 2 THEN t.Value END) as val_meas_2
FROM YourTable t
GROUP BY t.date

Upvotes: 1

James Z
James Z

Reputation: 12317

If you have just 1 row per date per measure, you can do it this way:

select
  Date,
  max(case when Measure = 1 then value end) as Value_Measure_1
  max(case when Measure = 2 then value end) as Value_Measure_2
from
  yourtable
group by
  Date

Upvotes: 2

Related Questions