Trying to split the misc_field_value column into several columns based on the misc_field_id value. The new column names aren't important. Column_1, Column_2 etc.
All of the answers I find are for splitting a column based on a space or values within the same column.
EDIT: I've tried:
select [10001], [10002], [10003], [10004], [10006]
from
(
select misc_field_id, misc_field_value
from #testtable
)d
pivot
(
max(misc_field_value)
for misc_field_id in ([10001], [10002], [10003], [10004], [10006])
)piv;
I guess I'm not sure what aggregate function to use? I'm actually trying to pivot about 5000 rows.
SQL Server 2014
+----+---------------+----------------------+
| ID | misc_field_id | misc_field_value |
+----+---------------+----------------------+
| 1 | 10001 | REIA EVENT |
| 1 | 10002 | Test2 REIA |
| 1 | 10003 | JOE Black |
| 1 | 10004 | Test Investing, LLC |
| 1 | 10006 | NC-1 |
| 2 | 10001 | BROKER |
| 2 | 10002 | TOM GOULD |
| 2 | 10004 | TEST Properties, LLC |
| 2 | 10006 | GA-1 |
| 3 | 10001 | CLIENT REFERRAL |
| 3 | 10002 | STEFAN TESTER |
| 3 | 10004 | SIVA EVANS |
| 3 | 10006 | GA-1 |
+----+---------------+----------------------+
The result I'm looking for would something like:
+----+-----------------+---------------+---------------------+----------+
| ID | Column_1 | Column_2 | Column_3 | Column_4 |
+----+-----------------+---------------+---------------------+----------+
| 1 | REIA EVENT | Test2 REIA | TEST INVESTING, LLC | NC-1 |
| 2 | BROKER | TOM GOULD | TEST Properties LLC | GA-1 |
| 3 | ClIENT REFERRAL | STEFAN TESTER | SIVA EVANS | GA-1 |
+----+-----------------+---------------+---------------------+----------+
