-1

I have got a table which got structure like this:-

chkey    chvalue        updatetime          unit_id
ch1       27            7/11/2018 1:08          35
ch2       89            7/12/2018 1:08          35
ch1       26            7/13/2018 2:08          46
ch2       77            7/14/2018 2:08          46
ch1       24            7/15/2018 3:08          47
ch2       77            7/16/2018 3:08          47

what i want is a query for generating a result like this:-

    updatetime          ch1     ch2     unit_id
    7/11/2018 1:08       27      89      35
    7/11/2018 2:08       26      77      46
    7/11/2018 3:08       24      77      47

NOTE: chkey's can be upto ch1....ch64 for any unit_id.

savya Dhurve
  • 49
  • 9

1 Answers1

0

Just use conditional aggregation:

select min(updatetime),
       max(case when chkey = 'ch1' then chvalue end) as ch1,
       max(case when chkey = 'ch2' then chvalue end) as ch2,
       unit_id
from t
group by unit_id;

You would just add 62 more expressions for each chkey.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • what if i say there are multiple rows of same unit_id and i want all of the data of that unit_id.....and yes tried this query with where clause like "where unit_id='35'" and what i want this is to be dynamic like any unit_id can have multiple chkey in this case it have only two chkey,some unit may have 3,4 or 64. – savya Dhurve Jul 12 '18 at 17:13
  • @savyaDhurve . . . Then I think you would have a different question. It is only possible to answer the question that you ask which is what this answer does. If you have another question, ask it as a *question* not a *comment*. – Gordon Linoff Jul 13 '18 at 02:20
  • the question link is [here](https://stackoverflow.com/questions/51322324/sql-query-for-concating-multiple-rows-with-same-ids-but-with-different-values-wh) – savya Dhurve Jul 13 '18 at 10:53