1

This question already has an answer here:

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.

marked as duplicate by Paul White Aug 15 '15 at 4:52

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

3

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
0

@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 '15 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 '15 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 '15 at 21:21

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