PIVOTing data with SQL server

Question

Okay, I have hit the bull's eye when I even didn't know how to fire a gun. I wrote this SQL based on Technet basic article ( http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx )

Select LogTime, Location, LA95Day, LA95Evening, LA95DEn, LA95Night from 
(Select LogTime, Location, LAValue, ValueType  from Project_5_CalculatedData 
     where ValueType in ('LA95Day','LA95Evening','LA95DEn','LA95Night')  )  as c
PIVOT(
Max(LAValue) For ValueType in (LA95Day,LA95Evening,LA95DEn,LA95Night)) as PVt

My Table structure is something like

DataID  Location    LogTime LAValue ValueType
10117   Meter1  2012-09-25 00:00:00.000 71.12   LA95Day
10118   Meter1  2012-09-25 00:00:00.000 55.52   LA95Evening
10119   Meter1  2012-09-25 00:00:00.000 52.69   LA95Night
10120   Meter1  2012-09-25 00:00:00.000 68.62   LA95Den
10121   Meter1  2012-09-26 00:00:00.000 72.21   LA95Day
10122   Meter1  2012-09-26 00:00:00.000 54.10   LA95Evening
10123   Meter1  2012-09-26 00:00:00.000 48.30   LA95Night
10124   Meter1  2012-09-26 00:00:00.000 69.38   LA95Den

EDIT: Data is incomplete here.. we have 4000 rows .. so you can assume MEter2, Meter3, Meter4 location will exists with same or different days as in sample data. ** Edit Ends **

we have upto 4 Meters to record the values, DataID is not important. However I want all Meter data to be aligned, the above query that I wrote does it's work partially, i.e. it shows data as :

LogTime Location    LA95Day LA95Evening LA95DEn LA95Night
2012-09-25 00:00:00.000 Meter1  71.12   55.52   68.62   52.69
2012-09-26 00:00:00.000 Meter1  72.21   54.10   69.38   48.30
2012-09-27 00:00:00.000 Meter1  68.88   47.77   66.05   46.02
2012-09-28 00:00:00.000 Meter1  73.52   49.23   70.56   43.60
2012-09-29 00:00:00.000 Meter1  54.09   44.87   52.85   41.64
2012-09-30 00:00:00.000 Meter1  51.46   48.61   51.94   41.60
2012-10-01 00:00:00.000 Meter1  73.09   51.78   70.17   46.20

But I want that based on meter used; LAday, evening, Den and night will get repeated up to 4 time; once per meter used in application. i.e. desire output [columns] needs to be

LogTime M1-LA95Day  M1-LA95Evening  M1-LA95DEn  M1-LA95Night M2-LA95Day M2-LA95Evening  M2-LA95DEn  M2-LA95Night M3-LA95Day M3-LA95Evening  M3-LA95DEn  M3-LA95Night M4-LA95Day M4-LA95Evening  M4-LA95DEn  M4-LA95Night

Oh, I am trying via hit and trial as I never use Pivot before and doesn't know even how my above query works, if you can just explain that query will help me do next step. thanks.

I am not 100% sure from your question what the issue is. If you are not seeing the repetition you are expecting, it is probably due to the use of Max(LAValue) rather than just selecting all values of LAValue. Can you add more data to your example that would yield the repetition you are looking for? You state that you want the values for "every meter used" but I only see one value for meter in your sample data set. — MystikSpiral
– MystikSpiral, Commented Sep 6, 2013 at 12:44
PIVOT query doesn't work [as in defination] without aggregate function, how can I choose them? Also I need them arrange for Location not Value, I use LaValue as it give that sample data. Oh, I make edit about Data, please review in question. Thanks. — Sumit Gupta
– Sumit Gupta, Commented Sep 6, 2013 at 12:52
Do you have a set number of meters? Or could it be an unlimited number of meters per location? — Taryn
– Taryn, Commented Sep 6, 2013 at 13:10
1 meter per location, and we have 4 meter in total. but maybe 2 are used, or all 4 are used is not fixed. — Sumit Gupta
– Sumit Gupta, Commented Sep 6, 2013 at 13:12
You need to manufacture a unique column name, which will allow you to aggregate, but only ever aggregate a single value. It gets you around the limitation of the PIVOT functionality which is otherwise awesome. Just make sure your unique column value nets you one per row you want in the resulting table. — MystikSpiral
– MystikSpiral, Commented Sep 6, 2013 at 15:46

Taryn · Accepted Answer · 2013-09-06 13:17:10Z

1

It seems to me that you are trying to list all meters along with their valueTypes as columns. If that is the case, then you should be able to use the following:

Select LogTime, 
    Meter1_LA95Day, Meter1_LA95Evening, Meter1_LA95DEn, Meter1_LA95Night,
    Meter2_LA95Day, Meter2_LA95Evening, Meter2_LA95DEn, Meter2_LA95Night,
    Meter3_LA95Day, Meter3_LA95Evening, Meter3_LA95DEn, Meter3_LA95Night,
    Meter4_LA95Day, Meter4_LA95Evening, Meter4_LA95DEn, Meter4_LA95Night 
from 
(
    Select LogTime, 
        ValueType = Location+'_'+ValueType,
        LAValue       
    from Project_5_CalculatedData 
    where ValueType in ('LA95Day','LA95Evening','LA95DEn','LA95Night')  
)  as c
PIVOT
(
    Max(LAValue) 
    For ValueType in (Meter1_LA95Day, Meter1_LA95Evening, Meter1_LA95DEn, Meter1_LA95Night,
                      Meter2_LA95Day, Meter2_LA95Evening, Meter2_LA95DEn, Meter2_LA95Night,
                      Meter3_LA95Day, Meter3_LA95Evening, Meter3_LA95DEn, Meter3_LA95Night,
                      Meter4_LA95Day, Meter4_LA95Evening, Meter4_LA95DEn, Meter4_LA95Night)
) as PVt;

Note, this assumes the meters are actually named meter1, meter2, etc. If your meters aren't called meter1, etc and they have names that might not be the same, then you might want to use dynamic SQL to get the result:

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Location+'_'+ValueType) 
                    from Project_5_CalculatedData
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT LogTime,' + @cols + ' 
            from 
            (
                Select LogTime, 
                    ValueType = Location+''_''+ValueType,
                    LAValue       
                from Project_5_CalculatedData 
                where ValueType in (''LA95Day'',''LA95Evening'',''LA95DEn'',''LA95Night'')  
            ) x
            pivot 
            (
                max(LAValue)
                for ValueType in (' + @cols + ')
            ) p '

execute sp_executesql @query;

These will give a result:

+-------------------------+----------------+----------------+--------------------+------------------+----------------+----------------+--------------------+------------------+----------------+----------------+--------------------+------------------+
|         LogTime         | Meter1_LA95Day | Meter1_LA95Den | Meter1_LA95Evening | Meter1_LA95Night | Meter2_LA95Day | Meter2_LA95Den | Meter2_LA95Evening | Meter2_LA95Night | Meter3_LA95Day | Meter3_LA95Den | Meter3_LA95Evening | Meter3_LA95Night |
+-------------------------+----------------+----------------+--------------------+------------------+----------------+----------------+--------------------+------------------+----------------+----------------+--------------------+------------------+
| 2012-09-25 00:00:00.000 | 71.12          | 68.62          | 55.52              | 52.69            | 71.12          | 68.62          | 55.52              | 52.69            | NULL           | NULL           | NULL               | NULL             |
| 2012-09-26 00:00:00.000 | 72.21          | 69.38          | 54.10              | 48.30            | NULL           | NULL           | NULL               | NULL             | 72.21          | 69.38          | 54.10              | 48.30            |
+-------------------------+----------------+----------------+--------------------+------------------+----------------+----------------+--------------------+------------------+----------------+----------------+--------------------+------------------+

answered Sep 6, 2013 at 13:17

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Sumit Gupta Over a year ago

Okay, that is perfect answer. Just one request can you explain me the part ValueType = Location+'_'+ValueType, I didn't understand that why you concatenate it ?

Taryn Over a year ago

@SumitGupta I concatenated them together to create your column headers. Since you want the meter number along with the valuetype, then need to be together in order to create the new header. :)

Sumit Gupta Over a year ago

now I am getting/understanding PIVOT tables better, so ValueType [after concatenation is what we define in IN clause, I didn't know that :( , but understand it now. Thanks again ...

Taryn Over a year ago

@SumitGupta Don't get discouraged, pivot is a difficult concept to understand. You'll get it! :)

Sumit Gupta Over a year ago

:) thanks for encourgement, I am at stage where no-one but stackoverflow that help me. happy to get lot of expert here like you.

Collectives™ on Stack Overflow

PIVOTing data with SQL server

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related