CODEWITHSUNDEEP
sqlsql-serverssms
lemon
lemon

Reputation: 187

Transform values into column name

What query sould I write to transfer this two tables:

Table1 - EventLog

ID          Timestamp                Type       EventGroupID
10920061    2021-03-22 10:58:44.527  202        3F23B45E-052C-463D-BA07-A6DEC3D1356E
10920062    2021-03-22 10:58:45.207  202        C5ACC8F3-7B73-4090-ADF5-CAA7D0284AFA
10920063    2021-03-22 10:58:49.440  202        CA5AE246-8D14-41F0-B3C8-382B43EE2F52

Table2 - EventLogData

EventID     K           V
10920061    action      LiveFeedStart
10920061    cameraId    513
10920061    tracker     True
10920061    username    xxx
10920062    action      LiveFeedStart
10920062    cameraId    90
10920062    tracker     True
10920062    username    xxx
10920063    action      LiveFeedStart
10920063    cameraId    200
10920063    tracker     True
10920063    username    xxx

Into something like this?

EventID   TimeStamp                Type   Action         Tracker  CameraId  Username
10920061  2021-03-22 10:58:44.527  202    LiveFeedStart  True     513       xxx
10920062  2021-03-22 10:58:45.207  202    LiveFeedStart  True     90        xxx

I was only able to produce this

EventID     Timestamp                   Type    K   V
12943429    2021-07-12 10:57:45.4433333 203 action  LiveFeedStop
12943429    2021-07-12 10:57:45.4433333 203 bytesSent   95440687
12943429    2021-07-12 10:57:45.4433333 203 cameraId    914
12943429    2021-07-12 10:57:45.4433333 203 username    xxx
12943430    2021-07-12 10:57:51.2800000 203 action  LiveFeedStop

Upvotes: 0

Views: 466

Answers (2)

Taryn
Taryn

Reputation: 247630

You can utilize the PIVOT function to get the result. Throwing your data into a couple of tables to test with:

CREATE TABLE EventLog
    ([ID] int, [Timestamp] datetime, [Type] int, [EventGroupID] varchar(36))
;
    
INSERT INTO EventLog
    ([ID], [Timestamp], [Type], [EventGroupID])
VALUES
    (10920061, '2021-03-22 10:58:44', 202, '3F23B45E-052C-463D-BA07-A6DEC3D1356E'),
    (10920062, '2021-03-22 10:58:45', 202, 'C5ACC8F3-7B73-4090-ADF5-CAA7D0284AFA'),
    (10920063, '2021-03-22 10:58:49', 202, 'CA5AE246-8D14-41F0-B3C8-382B43EE2F52')
;


CREATE TABLE EventLogData
    ([EventID] int, [K] varchar(8), [V] varchar(13))
;
    
INSERT INTO EventLogData
    ([EventID], [K], [V])
VALUES
    (10920061, 'action', 'LiveFeedStart'),
    (10920061, 'cameraId', '513'),
    (10920061, 'tracker', 'True'),
    (10920061, 'username', 'xxx'),
    (10920062, 'action', 'LiveFeedStart'),
    (10920062, 'cameraId', '90'),
    (10920062, 'tracker', 'True'),
    (10920062, 'username', 'xxx'),
    (10920063, 'action', 'LiveFeedStart'),
    (10920063, 'cameraId', '200'),
    (10920063, 'tracker', 'True'),
    (10920063, 'username', 'xxx')
;

You then use the PIVOT function to convert the row values into column names:

select *
from
(
  select 
    el.Id,
    el.[Timestamp],
    el.Type,
    eld.K,
    eld.V
  from EventLog el
  inner join EventLogData eld
    on el.Id = eld.EventId
) d
pivot
(
  max(V)
  for K in (Action, Tracker, CameraId, Username)
) piv

Gives the result you're looking for. (See demo)

Upvotes: 1

eshirvana
eshirvana

Reputation: 24568

select * from EventLog el
left join (
select eventId
       ,max(case when K = 'action' then V end) action 
       ,max(case when K = 'bytesSent' then V end) bytesSent 
       ,max(case when K = 'cameraId' then V end) cameraId 
       ,max(case when K = 'username' then V end) username 
from EventLogData
group by eventId 
) eld
on el.Id = eld=eventid

Upvotes: 3

Related Questions