Transpose SQL result set

Question

I have data in the following format:

Machine |  week_end  | widgets
 A      | 05-26-2013 | 5
 B      | 05-26-2013 | 4
 A      | 04-01-2013 | 6
 B      | 04-01-2013 | 0

I want my result set to look as follows:

Machine | 05-26-2013 | 04-01+2013 | ...
 A      | 5          | 6          |
 B      | 4          | 0          |

I'm not quite sure how to proceed here--how does one transpose a table like this without having to specify the week ends (or whatever) explicitly in the query? (DB is SQL Server 2008.)

score 2 · Accepted Answer · answered Sep 12 '13 at 13:26

2

You will need to do some research on the SQL PIVOT function. It will allow you to turn rows into columns. This should be what you are looking for, once you find out how to apply it.

Check out this link: Using PIVOT and UNPIVOT

answered Sep 12 '13 at 13:26

Nicholas Post

1,857
1
18
31

One question, though: I'd like to avoid having to type in the column names by hand, as there are lots of them. Is there a way to avoid this, or do I have to manually type in the col names? – BenDundee Sep 12 '13 at 13:34
No, you can add them dynamically. Try Googleing "Dynamic column names SQL pivot" or check out this link: http://stackoverflow.com/questions/7822004/pivots-with-dynamic-columns-in-sql-server – Nicholas Post Sep 12 '13 at 13:36

Transpose SQL result set

1 Answers1

Linked