0

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.

Improve this question
5
  • 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. 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. 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? 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. Commented Sep 6, 2013 at 13:12
  • 1
    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. Commented Sep 6, 2013 at 15:46

1 Answer 1

Reset to default
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 | +-------------------------+----------------+----------------+--------------------+------------------+----------------+----------------+--------------------+------------------+----------------+----------------+--------------------+------------------+
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

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 ?
@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. :)
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 ...
@SumitGupta Don't get discouraged, pivot is a difficult concept to understand. You'll get it! :)
:) thanks for encourgement, I am at stage where no-one but stackoverflow that help me. happy to get lot of expert here like you.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.