Sample Header Ad - 728x90

SQL: pivot non numeric data

2 votes
1 answer
1841 views
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