CODEWITHSUNDEEP
sqlsql-servert-sqlpivot
Abdussalam Mazhar
Abdussalam Mazhar

Reputation: 57

Pivot without using an aggregate function to show all values

I have a SQL table and given below is an image of it.

enter image description here

I need to use tablet names as column names and show below in rows their times. There are three distinct tablets and each has its own primary key.

Example column name augmenten and in this column shows all times of key 157. I want something like below

enter image description here

Upvotes: 0

Views: 348

Answers (1)

DhruvJoshi
DhruvJoshi

Reputation: 17126

You can try some thing like below

select [157],[158],[159] from
(
  select 
     koy, dateonly, time, 
     row_number() over(partition by koy order by dateonly desc,time desc) as ranking
from t
)s
pivot 
(
  max(time) for koy in ([157],[158],[159])
)p

The out put it gives me is below enter image description here

Reference queries used:

create table t(koy int, tablet varchar(10), [time] time,dayz varchar(20),dateOnly date);
insert into t values 
(157,'A','13:27:36 PM','Sat','03/12/2016'),
(157,'A','13:28:06 PM','Sat','03/12/2016'),
(157,'A','13:28:36 PM','Sat','03/12/2016'),
(158,'P','15:25:11 PM','Sat','03/12/2016'),
(158,'P','15:25:41 PM','Sat','03/12/2016'),
(158,'P','15:26:11 PM','Sat','03/12/2016'),
(159,'P','23:26:11 PM','Sat','03/12/2016')

Upvotes: 1

Related Questions