Turning a multiple row resultset into columns with multiple rows

Question

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.

score 3 · Accepted Answer · answered Aug 14 '15 at 19:38

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.

SELECT  
    Title
    ,[1]
    ,[2]
    ,[3]
FROM    
    (   
        SELECT  
            Title,
            Value, 
            ROW_NUMBER() OVER (PARTITION BY Title ORDER BY Title) AS RN
        FROM    
            YourResult
    ) RunInformation
    PIVOT (MIN(Value) FOR RN IN ([1],[2],[3])) AS PivotTable

score 0 · Answer 2 · edited Apr 13 '17 at 12:42

@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:

Why is the database in charge of UI concerns?
Why can't the application layer transform the output from a standard select into the desired visual layout?
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.

Turning a multiple row resultset into columns with multiple rows

2 Answers2