-1

I have such an SQL:

SELECT p.PID,
       p.HitIndex,
       p.Smiles,
       p.EntityID,
       pp.mw AS MW,
       pp.heavyatoms AS HeavyAtoms,
       pp.tpsa AS TPSA,
       pp.lipinskihba AS Lipinskihba,
       pp.lipinskihbd AS Lipinskihbd,
       pp.rotb AS RotB,
       pp.xlogp AS XlogP,
       pp.ro5 AS RO5,
       pp.bbbarcode AS BBBarcode
FROM (SELECT pr.PID,
             p.HitIndex,
             p.Smiles,
             p.EntityID,
             (CASE WHEN PropVal IS NULL THEN PropStrVal ELSE PropVal END) AS PropVal,
             PropName
      FROM V_DELProdProps pr
          JOIN DELProducts p ON p.PID = pr.PID) Temp
PIVOT (MAX(PropVal)
       FOR [PropName] IN ([mw], [heavyatoms], [tpsa], [lipinskihba], [lipinskihbd], [rotb], [xlogp], [ro5], [bbbarcode])) pp
    JOIN DELProducts p ON pp.PID = p.PID;
GO

What I want is to feed inside the MAX aggregation function float and nvarchar for different columns, but I am not sure how to do that correctly, how I can restructure the query.

Here is some data for V_DELProdProps:

enter image description here

And for DELProducts:

enter image description here

Here is an example output. The only thing that needed to be fixed here is getting correct values for the BBBarcode:

enter image description here

As you can see PIDs are not duplicate in the result and that's how it should be.

Nikita Vlasenko
  • 4,004
  • 7
  • 47
  • 87

2 Answers2

1

Use multi-pivot technique from this question In SQL Server how to Pivot for multiple columns:

with tmp as (
  select id, prop_name, prop_val, prop_str_val,
         prop_name as prop_name_bar
  from v_DelProdProps )
select id, max(tpsa) tpsa, max(rot) rot, max(bbbarcode) bbarcode from tmp
pivot (max(prop_val) for prop_name in ( [TPSA], [ROT]) ) as pvt1
pivot (max(prop_str_val) for prop_name_bar in ( [BBBarcode]) ) as pvt2
group by id

or use standard conditional aggregation:

select id, 
        max(case when prop_name = 'TPSA' then prop_val end) tpsa,
        max(case when prop_name = 'ROT' then prop_val end) rot,
        max(case when prop_name = 'BBBarcode' then prop_str_val end) tpsa
from v_DelProdProps 
group by id

or cast everything to nvarchars at first and do it like you did:

with tmp as (
  select id, prop_name, 
         case when prop_val is null then prop_str_val 
              else cast(prop_val as nvarchar(50)) 
         end prop_val
  from v_DelProdProps )
select * from tmp
pivot (max(prop_val) 
       for prop_name in ( [TPSA], [ROT], [BBBarcode]) ) as pvt

dbfiddle demo for all queries

In second solution you have to be careful, I don't know if for the same ID and prop_name there can be two entries, for example TPSA 70 and TPSA 500. In this case you need aggregation is needed at first, because '70' is greater than '500' when compared as strings.

I simplified your data, because most of the columns are not relevant to main problem.

Ponder Stibbons
  • 14,723
  • 2
  • 21
  • 24
0
SELECT p.PID, p.HitIndex, p.Smiles, p.EntityID,
    pp.mw as MW,
    pp.heavyatoms as HeavyAtoms,
    pp.tpsa as TPSA,
    pp.lipinskihba as Lipinskihba,
    pp.lipinskihbd as Lipinskihbd,
    pp.rotb as RotB,
    pp.xlogp as XlogP,
    pp.ro5 as RO5,
    pp.bbbarcode AS BBBarcode
FROM
(
    SELECT pr.PID, p.HitIndex, p.Smiles, p.EntityID,
        (CASE WHEN ISNUMERIC(PropVal) = 1 THEN CAST(PropVal AS FLOAT) ELSE NULL END) AS PropVal_Float,
        PropVal,
        PropName,
        bbbarcode AS BBBarcode
    FROM V_DELProdProps pr
    JOIN DELProducts p ON p.PID = pr.PID
) AS Temp
PIVOT
(
    MAX(PropVal_Float) FOR [PropName] In ([mw], [heavyatoms], [tpsa], [lipinskihba], [lipinskihbd], [rotb], [xlogp], [ro5], [BBBarcode])
) AS pp
JOIN DELProducts p ON pp.PID = p.PID;
Ashutosh Kumar
  • 161
  • 1
  • 13