Transform (easy) question

Question

TRANSFORM First (calc_flow_data.hourly_value) AS Firstofhourly_value
SELECT dfd.flow_day_and_hour
FROM rt_emeter_fmeter rtef
JOIN dt_elec_mtr_data demd ON rtef.location_meters_id = demd.location_meters_id
JOIN calc_flow_data cfd ON demd.emeter_id = dfd.emeter_id
JOIN dt_flow_data dfd ON cfd.flow_meter_id = dfd.flow_meter_id
WHERE cfd.energy_meter_descr = 'kwh'
GROUP BY dfd.flow_day_and_hour
ORDER BY dfd.flow_day_and_hour, rtef.location_id
PIVOT rtef.location_id

Error:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'cfd'.
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'PIVOT'.

I am trying to work on the correct coding for PIVOT, however I am uncertain to the first error with CFD.  I tried changing the second line ( FROM rt_emeter_fmeter rtef ) to calc_flow_data and still had the same issue.  Any thoughts?

Thanks,

Answer 1

Can you post the plain query before PIVOT and the output of it and how do you want to PIVOT it?

Your syntax right now is completely wrong for T-SQL. The T-SQL pivot is this:

;with cte as (Main Query Here)

select * from cte PIVOT (MAX(ValueColumn) FOR ColumnHeaders IN ([Col1],[Col2],[Col3])) pvt

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Hi Naomi,

I am trying to create a pivot table with the 'location_meter_id' across the columns and the 'hourly_value' for each row.

Answer 3

Can you show the output of your current query before pivot and how do you want to see the data after?

Also, perhaps a better idea will be to use SSRS Matrix option?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 4

TRANSFORM is Access syntax, there is no correspondence in SQL Server.

To run a PIVOT query in standard SQL, you do:

SELECT groupcol1, groupcol2,
       MIN(CASE WHEN pivotcol = 1 THEN pivotval END) AS pivot1,
       MIN(CASE WHEN pivotco2 = 1 THEN pivotval END) AS pivot2,
       ...
FROM   tbl
GROUP  BY groupcol1, groupcol2

T-SQL has a special PIVOT operator, but it is more difficult to understand and less flexible. Don't waste your time on it. The pattern above can easily be adjusted to sums, counts etc. And once you have learnt to master it, it's very simple to use.

It may look confusing with MIN here, but the aggregate only serves to make one row instead of many. But each MIN only sees one value, so MAX gives exactly the same result.

Note here that the query requires you to know all columns in advance. This is because the query generates a table, and a table is an entity with a distinct set of attributes. A dynamic pivot is at odds with relational theory. You can use dynamic SQL to get there - but if you can solve it elsewhere, that may be better.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 5

I think PIVOT syntax producing much shorter and simpler code if you need to pivot by one column only.

BTW, here is a good blog post about pivoting in T-SQL using 'old style' syntax:

Understanding SQL Server 2000 Pivot with Aggregates

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 6

Hi,

I see one issue there

DFD is referred before the it being defined in  the join, you may have to restructure your query little bit. I think there were similar questions discussed yesterday also

JOIN calc_flow_data cfd ON demd.emeter_id = dfd.emeter_id
JOIN dt_flow_data dfd ON cfd.flow_meter_id = dfd.flow_meter_id

Regards
Satheesh

Answer 7

here is the post

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/1634cb5e-dc87-4d13-91fb-11fcee69dbb0/#52f46f92-0031-43f1-8083-18e2db5e28d5

did you try any solutions from this thread?

Regards
satheesh

Answer 8

I think PIVOT syntax producing much shorter and simpler code if you need to pivot by one column only.

Dunno. Maybe shorter, if you want to pivot on specific values, but what if you want pivot, on say, an interval? Or a combination?

And shorter is not always simpler. Yes, those MIN(CASE WHEN THEN END) may be a little verbose, but once you've learnt it requires less thinking. And it may also be easier if you need a dynamic PIVOT - which people often seem to want.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 9

Hmm Erland,

I feel like I am not entirely sure I follow. From my code (changed so that it works) I am trying to set up a pivot table in which the rows are dfd.flow_day_and_hour and the columns are each of the rtef.location_id (there are a handful of them). And the value to be returned by both of them is the cfd.hourly_value (TRANSFORM First (calc_flow_data.hourly_value) AS Firstofhourly_value).  I have excluded the transform line from my code since I am not sure how to incorporate it.  I am trying to use the query you have but it doesn't seem to make sense to me (as of right now).  Thanks your advice so far - if you (or anyone else) have any other thoughts please let me know!

SELECT dfd.flow_day_and_hour

FROM rt_emeter_fmeter rtef
JOIN dt_elec_mtr_data demd ON rtef.location_meters_id = demd.location_meters_id
JOIN dt_flow_data dfd ON demd.emeter_id = dfd.emeter_id
JOIN calc_flow_data cfd ON cfd.flow_meter_id = dfd.flow_meter_id

WHERE cfd.energy_meter_descr = 'kwh'
GROUP by dfd.flow_day_and_hour
ORDER by dfd.flow_day_and_hour, rtef.location_id

Answer 10

Thanks Satheesh for re-referring me.  I have actually *finally* figured out the join and alias complications I was having.  Now the issue is to make the pivot / transform [transform isn't in sql] work for what I am trying to do. Regards, Jon

Answer 11

I believe I am pivoting multiple columns right? (i.e. location_id has many different ids)  So I should probably figure out this MIN (CASE WHEN THEN END) function you have described to me. 

e.g. something like this...

	loc_id1	loc_id2	loc_id3	loc_id4
1:00	hourly_value	hourly_value	hourly_value	hourly_value
2:00	hourly_value	hourly_value	hourly_value	hourly_value
3:00	hourly_value	hourly_value	hourly_value	hourly_value
4:00	hourly_value	hourly_value	hourly_value	hourly_value
5:00	hourly_value	hourly_value	hourly_value	hourly_value
6:00	hourly_value	hourly_value	hourly_value	hourly_value

Answer 12

Please post your current query (before pivot) and the output from it. Are the locations (which will become columns) known in advance?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 13

Hi Naomi,

Here is the queries (below - without the TRANSFORM function or the PIVOT function working) -- the predecessor queries are significantly longer sand probably not beneficial.

If by known, you mean that the titles of the location_ids are known - then yes. However, those location_ids can change depending on all the previous queries and the data input.  All in all - what I am hoping to do is list all the location_ids (>100 of them) along the column, the row titles would be the hour & date and then the hourly_value between the two would be the output.  Is this possible without having to write every case?

Thanks a lot - your help is greatly appreciated.  I am stumped at this point in time unfortunately.

SELECT dfd.flow_day_and_hour
FROM rt_emeter_fmeter rtef
JOIN dt_elec_mtr_data demd ON rtef.location_meters_id = demd.location_meters_id
JOIN dt_flow_data dfd ON demd.emeter_id = dfd.emeter_id
JOIN calc_flow_data cfd ON cfd.flow_meter_id = dfd.flow_meter_id

WHERE cfd.energy_meter_descr = 'kwh'
GROUP by dfd.flow_day_and_hour
ORDER by dfd.flow_day_and_hour,
PIVOT (rtef.location_id)

Answer 14

Can you please show the current output of this query and how do you want to get it transformed (show both your current output and the output you want):

SELECT dfd.flow_day_and_hour
FROM rt_emeter_fmeter rtef
JOIN dt_elec_mtr_data demd ON rtef.location_meters_id = demd.location_meters_id
JOIN dt_flow_data dfd ON demd.emeter_id = dfd.emeter_id 
JOIN calc_flow_data cfd ON cfd.flow_meter_id = dfd.flow_meter_id

WHERE cfd.energy_meter_descr = 'kwh'
GROUP by dfd.flow_day_and_hour
ORDER by dfd.flow_day_and_hour

Right now you only return 1 column - usually for pivoting you need at least 2 and better 3 columns.

I need to see what you're getting at present and how do you want to show the data.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 15

Hi Naomi,

The results from the current query are as follows.  I was hoping to have the rtef.location_meters_id along the top and calc_flow_data.hourly_value be the value in the table.  Do you have any thoughts?

flow_day_and_hour

2008-01-01 00:00:00

2008-01-01 01:00:00

...

until

2010-12-30 00:00:00

Answer 16

From this output what OUTPUT do you want? You only showed me your current output and in this current output I only see the values for the day and hour and I don't see any locations here. You need to get that column also in your original output before you can do any manipulation with it.

Anyway, I am repeating the same question 4th time in this thread - give me your output with all relevant columns and desired output.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 17

Hi Naomi,

Thank you for your patience.  Below is the additional columns which are included.  What I would like to have is the location_meters_id running along as the column titles and the hourly value in the center grid.

SELECT dfd.flow_day_and_hour, rtef.location_meters_id, cfd.hourly_value
FROM dt_flow_data dfd
JOIN calc_flow_data cfd ON cfd.flow_meter_id = dfd.flow_meter_id
JOIN dt_elec_mtr_data demd ON demd.emeter_id = dfd.emeter_id
JOIN rt_emeter_fmeter rtef ON rtef.location_meters_id=demd.location_meters_id

WHERE cfd.energy_meter_descr = 'kwh'
GROUP by dfd.flow_day_and_hour, rtef.location_meters_id, cfd.hourly_value
ORDER by dfd.flow_day_and_hour

OUTPUT:

flow_day_and_hour       location_meters_id              hourly_value

2008-01-01 00:00:00           67                                       156.9763

2008-01-01 00:00:00            84                                          NULL

2008-01-01 00:00:00            87                                        NULL

2008-01-01 00:00:00             95                                        98.76                                        

etc.

This does this for every hour (over the time period), and every location_meters_id.

The goal is to have every hour stay the same -- but have each location_meters_id across as the column headers and the hourly value which corresponds respectively between the two points.

Thanks again - am I explaining this properly, apologies if it is confusing.

My sense is it is a very simple syntax command that I am unaware of.

Answer 18

In this case try:

;with cte as (SELECT dfd.flow_day_and_hour, rtef.location_meters_id, cfd.hourly_value
FROM dt_flow_data dfd
JOIN calc_flow_data cfd ON cfd.flow_meter_id = dfd.flow_meter_id
JOIN dt_elec_mtr_data demd ON demd.emeter_id = dfd.emeter_id 
JOIN rt_emeter_fmeter rtef ON rtef.location_meters_id=demd.location_meters_id

WHERE cfd.energy_meter_descr = 'kwh') -- this is your current query
SELECT * from cte PIVOT (sum(Hourly_Value) FOR Location_Meters_ID IN ([67],[84],[87],[95])) pvt

The output of this query will be

flow_day_and_hour        67                         84                                87                            95

2008-01-01 00:00:00      156.9763               NULL                          NULL                       98.76

-------------

This query assumes you know in advance your location_ids. If you don't know them, then we need to use dynamic PIVOT, which is slightly more complex, and I am not sure you're at this stage yet.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 19

Hi Naomi,

Thanks for the code / information regarding the query. 

That was what I was afraid of in terms of 'knowing' the values.  It is necessary to do a dynamic query as I have over 100 location_meters_id which will be changing, and if manually entered is a long data entry process (i.e. 

SELECT * from cte PIVOT (sum(Hourly_Value) FOR Location_Meters_ID IN ([67],[84],[87],[95])) pvt 

.for >100.

I will go find out what i can re:dynamic queries on the internets.

Thanks again,

Jon

Answer 20

This is how to make it dynamically:

declare @SQL nvarchar(max), @Cols nvarchar(max)

IF OBJECT_ID('tempDB..#TempResults',N'U') IS NOT NULL
DROP TABLE #TempResults

SELECT dfd.flow_day_and_hour, rtef.location_meters_id, cfd.hourly_value

INTO #TempResults -- Save current output into temp table -- hopefully not too much data

FROM dt_flow_data dfd
JOIN calc_flow_data cfd ON cfd.flow_meter_id = dfd.flow_meter_id
JOIN dt_elec_mtr_data demd ON demd.emeter_id = dfd.emeter_id 
JOIN rt_emeter_fmeter rtef ON rtef.location_meters_id=demd.location_meters_id

WHERE cfd.energy_meter_descr = 'kwh'

select @Cols = stuff((select ', ' + quotename(LocID) 
from (select distinct Location_Meters_ID,
cast(Location_Meters_ID as varchar(20)) as LocID
from #TempResults) src 
ORDER BY Location_Meters_ID
FOR XML PATH('')),1,2,'')

SET @SQL = 'SELECT * FROM #TempResults
PIVOT (sum(Hourly_Value) FOR Location_Meters_ID IN (' + @Cols + ')) pvt'

PRINT @SQL -- for verification only

EXECUTE (@SQL)

This is from the top of my head.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 21

I see what you mean as it being more complicated. I am going to try and work through this.  Thanks very much - greatly appreciated!

Jon

Answer 22

This is an easy question - however I haven't been able to figure it out -- why use the @ symbols and the # symbols?

Thanks

Answer 23

@ used with the local variables (and table variables). @@ is used for system variables such as @@ERROR, @@ROWCOUNT, etc.

# is used for temp tables (the temporary tables created in tempDB database that exist only for the session).

## used for global temp table (the temp table which is shared between all sessions created after the first session created it).

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog