CODEWITHSUNDEEP
sqlpivotaggregate
Jay Dee
Jay Dee

Reputation: 63

Transpose multiple rows to multiple columns with no aggregate using T-SQL

Users have multiple certificates which might be of 3 different types. They may hold multiple certificates of those types. I would like to put them into a single record anticipating that they will have a max of five certificates of each type. I've written a query that will find the type and put its information into an appropriately named column but still get one row per certificate. Data shape is:

Name, cert_Type, Cert Name, cert_State, Cert_Expiration
JOE, Equipment, NULL, Operator, 01/30/2022
JOE Equipment, Rigger, 12/31/2021
JOE License, Maryland, 08/12/2025

I'm doing a group by, but still need some aggregating function to get the desired result which might look like this:

| UserName| userID|Cred_Type_1|Cred_1|Type_1_Cred_1_ Expires|Type_1_Cred_2|Type_1_Cred_2_Expires|Cred_type_2|Type2_State |Type_2_expires|
| ----------- | ----------- |-------------|-------------|------------|------------|-----------|-----------|---|---|
|Joe|123|Equipment|Operator|01/30/2022|Rigger|12/31/2021|License | Maryland|08/12/2025|

Note that there is no aggregate here, not counting or averaging or summing. Is there another aggregate function that will do this?

Upvotes: 0

Views: 137

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

If I understand correctly, you can use row_number() and conditional aggregation:

select userid, username,
       max(case when seqnum = 1 then cert_type end),
       max(case when seqnum = 1 then cert_name end),
       max(case when seqnum = 1 then cert_state end),
       max(case when seqnum = 1 then cert_expiration end),
       max(case when seqnum = 2 then cert_type end),
       max(case when seqnum = 2 then cert_name end),
       max(case when seqnum = 2 then cert_state end),
       max(case when seqnum = 2 then cert_expiration end),
       max(case when seqnum = 3 then cert_type end),
       max(case when seqnum = 3 then cert_name end),
       max(case when seqnum = 3 then cert_state end),
       max(case when seqnum = 3 then cert_expiration end),
from (select t.*,
             row_number() over (partition by userid order by cert_expiration desc) as seqnum
      from t
     ) t
group by userid, username;

Upvotes: 1

Related Questions