I have a set of data which basically looks like the following:
-sql
+---------+--------+
| EventId | Field |
+---------+--------+
| 1 | apple |
| 1 | paper |
| 1 | boy |
| 2 | banana |
| 2 | cat |
| 3 | girl |
+---------+--------+
I would like to pivot this to look like this:
-sql
+-------+--------+------+
| 1 | 2 | 3 |
+-------+--------+------+
| apple | banana | girl |
| paper | cat | |
| boy | | |
+-------+--------+------+
I am trying to fiddle around PIVOT
, but I can't get the result I want.
Am I looking at or using the wrong command? Is PIVOT
the correct relational operator for this?
This is the code I have been working on so far:
-sql
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', ' + QUOTENAME(EventID)
FROM (
select distinct
EventId
from Events
) AS x;
SET @sql =
N'select ' +
STUFF(@columns, 1, 2, '') + '
into ##tempMap from
(
select
ev.EventID,ev.FieldName
from Events ev
) as a
pivot
(
max(FieldName) for EventId in ('
+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
+ ')
) as pivottable;';
EXEC sp_executesql @sql
select * from ##tempMap
drop table ##tempMap
The code above returns the following results because of the MAX
aggregate function.
-sql
+-------+-----+------+
| 1 | 2 | 3 |
+-------+-----+------+
| paper | cat | girl |
+-------+-----+------+
Asked by Suresh Kinta
(23 rep)
Aug 28, 2020, 11:00 AM
Last activity: Sep 1, 2020, 06:31 AM
Last activity: Sep 1, 2020, 06:31 AM