sqlsql-servert-sqlfiltered

SQL Server : filtered rows to column

I get below Table A using

select sn, processName, result
from xxxx
inner join xxxx on xxxx
inner join xxxx on xxxx
where processDate between xxx  xxx

Table A

sn processName result
a01 depthMeas 0.1
a01 widthMeas 2
a01 weight 11.6
b02 depthMeas 0.2
b02 widthMeas 2.1
b02 weight 11.3
a24 depthMeas 0.15
a24 widthMeas 2.2
a24 weight 11.5

How can I transform Table A or directly get table like below?

sn depthMeas widthMeas weight
a01 0.1 2.0 11.6
b02 0.2 2.1 11.3
a24 0.15 2.2 11.5

Probably I can transform it using where clause and pivot. But it will have to be ran multiple (3 times) of query which will take long time (since my records are around 350,000,000)

Power BI, I am loading 3 times of native query table.

  1. 'depthMeas' filtered table
  2. 'widthMeas' filtered table
  3. 'weight' filtered table.

and then merge them into one table. This takes bunch of times on native query process.

Thanks in advance

Solution

  • You can use GROUP BY, as in:

    with
q as (
  -- your query here
)
select
  sn,
  max(case when processname = 'depthMeas' then result end) as depthmeas,
  max(case when processname = 'widthMeas' then result end) as widthmeas,
  max(case when processname = 'weight' then result end) as weight
from q
group by sn