objects with same id returned in one row
-
Hello,
I have table structure like this:
call_id
event
param1
param2
param3
1
1
x
r
s
1
2
y
g
f
1
3
z
n
d
I would like to have all data for the same call id in one row, for example:
call_id
event_1
param1_1
param2_1
param3_1
event_2
param1_2
param2_2
param3_2
event_3
param1_3
param2_3
param3_3
1
1
x
r
s
2
y
g
f
3
z
n
d
Please advise.
Regards, Saša
-
If the table is fixed as specified then either of these
SELECT call_id,
1 AS [event_1],
MAX(CASE WHEN event=1 THEN param1 ELSE null END) AS [param1_1],
MAX(CASE WHEN event=1 THEN param2 ELSE null END) AS [param2_1],
MAX(CASE WHEN event=1 THEN param3 ELSE null END) AS [param3_1],
2 AS [event_2],
MAX(CASE WHEN event=2 THEN param1 ELSE null END) AS [param1_2],
MAX(CASE WHEN event=2 THEN param2 ELSE null END) AS [param2_2],
MAX(CASE WHEN event=2 THEN param3 ELSE null END) AS [param3_2],
3 AS [event_3],
MAX(CASE WHEN event=3 THEN param1 ELSE null END) AS [param1_3],
MAX(CASE WHEN event=3 THEN param2 ELSE null END) AS [param2_3],
MAX(CASE WHEN event=3 THEN param3 ELSE null END) AS [param3_3]
FROM
GROUP BY call_id
ORDER BY call_id
SELECT e1.call_id,
e1.event AS [event_1],
e1.param1 AS [param1_1],
e1.param2 AS [param2_1],
e1.param3 AS [param3_1],
e2.event AS [event_2],
e2.param1 AS [param1_2],
e2.param2 AS [param2_2],
e2.param3 AS [param3_2],
e3.event AS [event_3],
e3.param1 AS [param1_3],
e3.param2 AS [param2_3],
e3.param3 AS [param3_3]
FROM e1
INNER JOIN e2 ON e2.call_id = e1.call_id AND e2.event = 2
INNER JOIN e3 ON e3.call_id = e1.call_id AND e3.event = 3
WHERE e1.event = 1
ORDER BY e1.call_id
however if the number of columns is variable then you will need to use dynamic sql
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply