I currently have a resultset that looks like this:

Title                   Value
Start Time              1998-01-04 01:38:00.000
End Time                1998-01-04 01:43:00.000
Product Name            TESR                               
Run Time                325
Delay Time              0
Shutdowns               0
Shift                   2
Gross MSF               3.791682
Start Time              1998-08-12 12:43:00.000
End Time                1998-08-13 16:48:00.000
Product Name            FCC                               
Run Time                320
Delay Time              0
Shutdowns               0
Shift                   1
Gross MSF               3.791682
Start Time              1999-11-04 02:43:00.000
End Time                1999-11-04 03:48:00.000
Product Name            1/2" TESR                               
Run Time                325
Delay Time              0
Shutdowns               0
Shift                   2
Gross MSF               3.791682

What I would like to end up with is this:

Start Time              1998-01-04 01:38:00.000      1998-08-12 12:43:00.000
End Time                1998-01-04 01:43:00.000      1998-08-13 16:48:00.000
Product Name            TESR                         FCC
Run Time                325                          325
Delay Time              0                            0
Shutdowns               0                            0
Shift                   2                            2
Gross MSF               3.791682                     3.121

Basically looking like a spreadsheet with each group of records being put in a column to the right.

The original result is from a pivot of a flat table joined to a sub table that puts a title to the property fields. It is running on SQL Server 2008 R2. The data is coming from an HMI system and the client wants to see the data in the "spreadsheet" format.


2 Answers 2


You could try Pivot: I think you'll need to order on some key that ties these records together (how do you know that the starttime of "TESR" is related to the EndTime of "TESR". That probably needs to be in the ORDER BY clause below.

    ) RunInformation
    PIVOT (MIN(Value) FOR RN IN ([1],[2],[3])) AS PivotTable

@PaulBarbin's answer is great if you know that you will always need to output three columns of data. If you need to dynamically output a different number of columns based on the data being selected then you will have to adapt his solution using dynamic sql.

Effectively you are trying to get SqlServer to output data contrary to what its natural inclination is. This can be done but when you fight your tools you bring grief on yourself. I would try to talk to your client/PM and figure out:

  1. Why is the database in charge of UI concerns?
  2. Why can't the application layer transform the output from a standard select into the desired visual layout?
  3. If the database must produce output that a spreadsheet can natively handle can you output a CSV file or the equivalent string, since all spreadsheet programs should be able to read a csv file.
  • I agree Erik, I am going back to the client with the concerns about having to change the select statements every time they change what they want included. I have marked Paul's answer as correct as it does return the resultset in the the requested format.
    – M. Maynard
    Aug 14, 2015 at 20:23
  • @PaulBarbin whipped out that query much faster than I could have. :) He definitely deserves the correct answer. I also definitely agree that talking to your client more is the best long term solution. Best of luck!
    – Erik
    Aug 14, 2015 at 20:33
  • For the record, I don't use Pivot much and had to rely on an example query I saved just to get the syntax right! While I'm on the record, I do agree that we shouldn't have to bend over backwards with the language (Pivot is not part of the ANSI Standard). Probably a good indication to use a different tool.
    – paulbarbin
    Aug 14, 2015 at 21:21

Not the answer you're looking for? Browse other questions tagged or ask your own question.