SQL Server 2014 - displaying vertical data in horizontal format

Question

This is my first time to post on stackoverflow. I hope I’m doing it correctly.

I have a SQL Server 2014 table that contains “verticle” data I need to rotate into a single record with multiple columns. bluefeet's answer (SQL Server 2008 Vertical data to Horizontal) almost worked me, but not quite. I have temp table “#Columns,” which contains a list of column names that can change based on the data, and table “Transaction,” which contains my data. Here are the contents of the #Columns table:

ColumnName SeqNum
Time 1
Area 2
Density 3
Ref Index 4
H2SO4 5
pH 6
Urea 7
AN 8
TN 9
Ratio 10
NH3 11
SpGr 12
Cl2 13
Fe 14
Gross 15
Comment 99

Here is the relevant data from the Transaction table:

TransactionId   SamplePoint_Id  SP_Chem_Id  EntryDate   Time    Chem_Id Value   Comment
636 18  108 7/19/2017   1219    21  1   NULL<br>
637 18  109 7/19/2017   1219    22  2   NULL<br>
638 18  110 7/19/2017   1219    28  3   NULL<br>
639 18  111 7/19/2017   1219    1   4   NULL<br>
640 18  112 7/19/2017   1219    51  693.51  NULL<br>
641 18  113 7/19/2017   1219    2   -442.68 NULL<br>
642 18  114 7/19/2017   1219    61  168.58  NULL<br>
643 18  115 7/19/2017   1219    52  -0.64   NULL<br>
644 18  116 7/19/2017   1219    4   0.1 NULL<br>
645 18  117 7/19/2017   1219    62  1.009   NULL<br>
646 18  NULL    7/19/2017   1219    54  5   NULL<br>
647 18  NULL    7/19/2017   1219    12  6   NULL<br>
648 18  NULL    7/19/2017   1219    33  7   NULL<br>
649 18  NULL    7/19/2017   1219    70  NULL    8.88889E+12<br>
650 18  NULL    7/19/2017   1219    71  NULL    NULL<br>
651 18  108 7/19/2017   1220    21  2   NULL<br>
652 18  109 7/19/2017   1220    22  3   NULL<br>
653 18  110 7/19/2017   1220    28  4   NULL<br>
654 18  111 7/19/2017   1220    1   5   NULL<br>
655 18  112 7/19/2017   1220    51  1413.51 NULL<br>
656 18  113 7/19/2017   1220    2   -729.68 NULL<br>
657 18  114 7/19/2017   1220    61  404.01  NULL<br>
658 18  115 7/19/2017   1220    52  -0.52   NULL<br>
659 18  116 7/19/2017   1220    4   0.07    NULL<br>
660 18  117 7/19/2017   1220    62  2.018   NULL<br>
661 18  NULL    7/19/2017   1220    12  7   NULL<br>
662 18  NULL    7/19/2017   1220    54  8   NULL<br>
663 18  NULL    7/19/2017   1220    33  9   NULL<br>
664 18  NULL    7/19/2017   1220    70  NULL    12341234<br>
665 18  NULL    7/19/2017   1220    71  NULL    NULL<br>

Here is my query:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(ColumnName)
                    from #Columns
                    group by ColumnName, SeqNum
                    order by SeqNum
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

    set @query = N'SELECT ' + @cols + ' 
            from 
            (
                SELECT 
                c1.ColumnName,
                case when len(t.Comment) > 0 then t.Comment when c1.ColumnName = ''Time'' then t.Time else t.Value end as Value
                FROM [Transaction] t
                join Chemical c on t.Chem_Id = c.ChemicalId    
                join SamplePoint sp on t.SamplePoint_Id = sp.SampleId
                join Source s on sp.Source_Id = s.SourceId         
                join #Columns c1 
                ON c.Abbrev = c1.ColumnName 
                where s.Name = ''' + @SourceName + ''' and t.EntryDate = ''' + @Date + '''
            ) x
            pivot 
            (
                max(Value)
                for ColumnName in (' + @cols + ')
            ) p '

--print @cols
--print @query
execute sp_executesql @query;

And here is my result:

Time    Area    Density Ref Index   H2SO4   pH  Urea    AN  TN  Ratio   NH3 SpGr    Cl2 Fe  Gross   Comment<br>
1220    NULL    2   3   4   5   693.51  -729.68 404.01  -0.64   0.1 2.018   8   7   9   8888888888888

The problems is that I'm only getting one record back from my query, when what I want is two records - one for Time = 1219 and one for Time = 1220. I know (or think) the problems is the aggregate function MAX, which causes the query to return the record with the “max value.” How do I get both records? I can't get the pivot function to work without the aggregate function. Thanks.

Answer 1

You still have not excluded time from your PIVOTed columns. Note that when I say exclude from PIVOT I don't just mean that you remove them from the PIVOT ... IN list but that you also change your inner query to return time column as a separate column. That is why I suggested to re-read carefully MSDN Pivot docs to understand how PIVOT function works.

Inner query SELECT should be changed to this (Time is returned as a separate column, and CASE has no mention of it):

SELECT t.TIME, c1.ColumnName,
    CASE 
        WHEN len(t.Comment) > 0     THEN t.Comment
        ELSE t.Value
    END AS Value

Pivot part should become this (no Time column):

pivot ( max(Value) for ColumnName in ([Area], [Density], [Ref Index], [H2SO4], [pH], [Urea], [AN]], [TN], [Ratio], [NH3], [SpGr], [Cl2], [Fe], [Gross], [Comment]) ) p 

Remove time from your #Columns table.