SQL to transpose table

Question

Looking for a simple way to kind-of transpose a table to be able to further analyze data in software with no DB capabilities.

Table:

Id  testdate partid testid
1  2-13-2014  45  58
2  2-23-2014  45  2

I want to extract a table from this that puts testid in the column name and date in its fields, thus looks like this:

partid  test-1  test-2  test-3  ...  test-58  ...
45                   2-23-2014                        2-13-2014

There could be a few 100 testid's. I plan to expand the code to multiple columns per testid, eg: test-1-date test-1-result test-1-success.

Prefer common SQL, but if it has to be specific I'd be MS SQL server.

Unless you have the list of all the tests in advance, you have to do this using dynamic SQL. There is no such thing as a "variable" column name. — Gordon Linoff, Mar 29 '14 at 22:47
Is using only SQL mandatory? I suggest you to copy the tables in excel and transpose them there. — Antonio Ragagnin, Mar 29 '14 at 22:50
Try using a Pivot in sql like http://stackoverflow.com/questions/14618316/how-to-create-a-pivot-query-in-sql-server-without-aggregate-function — Mandar, Mar 29 '14 at 22:59
possible duplicate of [Transposing a table in SQL server](http://stackoverflow.com/questions/8429442/transposing-a-table-in-sql-server) — Ken White, Mar 31 '14 at 17:56

score 0 · Accepted Answer · edited May 23 '17 at 10:26

Okay, found it, its "PIVOT" that I needed. When searching PIVOT there are many questions and answers on this site.

One thing that is different in my case compared with most other PIVOT examples is that I try to aggregate strings rather than numbers. (Think of the date field as a string)

Specifically helpful were:

How to create a pivot query in sql server without aggregate function

Get ROWS as COLUMNS (SQL Server dynamic PIVOT query)

Convert Rows to columns using 'Pivot' in mssql when columns are string data type

Pivot table strings grouping under pivot column?

SQL to transpose table

1 Answers1