-1

I am trying to write a query to transpose rows to columns. The attached image has a sample table which I want to transpose and expected output.

Any input is appreciated.

Sample table and expected output

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Anupriya Khare
  • 1
  • 1
  • Use conditional aggregation for this. It has been asked and answered hundreds and hundreds of times around here. – Sean Lange Jun 05 '19 at 20:27
  • It looks like you want to pivot your table twice. Try pivoting it once first: https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-2017 – reasonet Jun 05 '19 at 20:52
  • Also see: https://stackoverflow.com/a/13377114/2283168 – reasonet Jun 05 '19 at 20:54

1 Answers1

0

Option 1. Conditional aggregation

select labid, subjectid,
max(case when timepoint='T0' then val1 end) T0_val1,
max(case when timepoint='T0' then val2 end) T0_val2,
max(case when timepoint='T1' then val1 end) T1_val1,
max(case when timepoint='T1' then val2 end) T1_val2
from input
group by labid, subjectid;

Option 2. Unpivot/Pivot

select * from
(
select labid, subjectid, timepoint+'_'+col as timepoint_col, val
from
(select labid, subjectid, timepoint,  val1, val2
from input) as src
unpivot 
(
  val for col in (val1, val2) 
) as unpiv) as x
pivot
(
max(val)
 for timepoint_col in ([T0_val1],[T0_val2],[T1_val1],[T1_val2])
) as piv1
arturro
  • 1,598
  • 1
  • 10
  • 13