Pivote Help

Question

Hi good Afternoon guys

Here i am having some problem SQL pivot 

my qyuery is like this

select 
kwh_imp as [kwh_imp],
kvah_Exp as [kvah_Exp],
kvah_imp as [kvah_imp],
kvarh_lag_imp as [kvarh_lag_imp],
kvarh_lag_imp as [kvarh_lag_imp],
kvarh_lag_exp as [kvarh_lag_exp],
kvarh_lead_exp as [kvarh_lead_exp] 
from (select Meter_Name,kwh_imp,kvah_Exp,kvah_imp,kvarh_lag_imp,kvarh_lag_imp,kvarh_lag_exp,kvarh_lead_exp from [EMS_Network].[dbo].[L4realtime]) q1
pivot(sum(kwh_imp,kvah_Exp,kvah_imp,kvarh_lag_imp,kvarh_lag_imp,kvarh_lag_exp,kvarh_lead_exp)
for Meter_Name in (kwh_imp,kvah_Exp,kvah_imp,kvarh_lag_imp,kvarh_lag_imp,kvarh_lag_exp,kvarh_lead_exp)) as pvt

and here i am getting error like 

Msg 102, Level 15, State 1, Line 10
Incorrect syntax near ','.

please help me for that

---

Pramod

Answer 1

Try this:

select 
kwh_imp as [kwh_imp],
kvah_Exp as [kvah_Exp],
kvah_imp as [kvah_imp],
kvarh_lag_imp as [kvarh_lag_imp],
kvarh_lag_imp as [kvarh_lag_imp],
kvarh_lag_exp as [kvarh_lag_exp],
kvarh_lead_exp as [kvarh_lead_exp] 
from (select Meter_Name,kwh_imp,kvah_Exp,kvah_imp,kvarh_lag_imp,kvarh_lag_imp,kvarh_lag_exp,kvarh_lead_exp from [EMS_Network].[dbo].[L4realtime]) q1
pivot(sum(kwh_imp+kvah_Exp+kvah_imp+kvarh_lag_imp+kvarh_lag_imp+kvarh_lag_exp+kvarh_lead_exp)
for Meter_Name in (kwh_imp,kvah_Exp,kvah_imp,kvarh_lag_imp,kvarh_lag_imp,kvarh_lag_exp,kvarh_lead_exp)) as pvt

Why you need to sum on all those columns? Are you sure all those columns are numeric? Usually the sum is done for one column only. Ex:

select 
kwh_imp as [kwh_imp],
kvah_Exp as [kvah_Exp],
kvah_imp as [kvah_imp],
kvarh_lag_imp as [kvarh_lag_imp],
kvarh_lag_imp as [kvarh_lag_imp],
kvarh_lag_exp as [kvarh_lag_exp],
kvarh_lead_exp as [kvarh_lead_exp] 
from (select Meter_Name,kwh_imp,kvah_Exp,kvah_imp,kvarh_lag_imp,kvarh_lag_imp,kvarh_lag_exp,kvarh_lead_exp from [EMS_Network].[dbo].[L4realtime]) q1
pivot(sum(some_column_here)
for Meter_Name in (kwh_imp,kvah_Exp,kvah_imp,kvarh_lag_imp,kvarh_lag_imp,kvarh_lag_exp,kvarh_lead_exp)) as pvt

Answer 2

hi irusul good morning 

all those columns are flote data type

now my data is like this way and i want to show this data meter_name wise 

it means row based data should  display column wise.......

---

Pramod

Answer 3

hey isusul 

i am doing this query like this way 

select 
U1C as [U1C],
U2C as [U2C],
U3C as [U3C],
U4C as [U4C],
U5C as [U5C],
U6C as [U6C],
U7C as [U7C] 
from (select Meter_Name,kwh_imp from [EMS_Network].[dbo].[L4realtime]) q1
pivot(sum(kwh_imp)
for Meter_Name in (U1C,U2C,U3C,U4C,U5C,U6C,U7C)) as pvt

and i got output like this way 

but irusul tiill yet i cant understand i want more column in ("SUM") 

and one more thing when agrigate function use like SUM,MAX,MIN,AVG,etc any suggestion from you. because of here lots of data manipulate on client bases 

more important is when we have to use agrigate function what types of agrigate function use in Pivot ....    

---

Pramod

Answer 4

Hello Pramod,

Your thread is more related to the creation of a query. If you post your agreement, a moderator will move ASAP your thread towards the TRANSACT-SQL Forum.No thread to recreate, no lost posts and i think it will interest more people in the Transact-SQL forum that in this forum ( and you will get a quicker and fuller answer ).

We are waiting for your feedback to try to help you more efficiently.

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 5

hi Papy i will post my my thred to TRANSACT-SQL Forum

---

Pramod

Answer 6

hi forums user i was suffering some another problem like when i pivot some table that time getting some error related to incoreect syntax near by ',' and really i dont know the use agrigate function.

i cant understand i want more column in ("SUM") where i am using SUM function in Pivot

and one more thing when agrigate function use like SUM,MAX,MIN,AVG,etc any suggestion from you. because of here lots of data manipulate on client bases 

more important is when we have to use agrigate function what types of agrigate function use in Pivot .... 

---

Pramod

Answer 7

Hi,

Please be more specific - post your SQL code and copy the error message as well.

Thanks,

Zoli

Answer 8

OK 

My concern is like 

select 
U1C as [U1C],
U2C as [U2C],
U3C as [U3C],
U4C as [U4C],
U5C as [U5C],
U6C as [U6C],
U7C as [U7C] 
from (select Meter_Name,kwh_imp from [EMS_Network].[dbo].[L4realtime]) q1
pivot(max(kwh_imp)
for Meter_Name in (U1C,U2C,U3C,U4C,U5C,U6C,U7C)) as pvt

this my simple query and i got output also like 

and my data is like this way

here i want to show multiple Kwh_imp,kvarh_exp........all parametrs....

using Agrigate function .....

---

Pramod

Answer 9

Hi Pramod,

See if this post useful to you

http://beyondrelational.com/modules/2/blogs/115/posts/15909/transpose-whole-table.aspx

---

- Chintak (My Blog)

Answer 10

in order by have aggregate functions, you have to group by on that field, i.e. it works on column wise. Do you mean to say that you need the sum of all values in the rows after pivot, then max of those values in the same row etc etc.

regards

joon

Answer 11

Hello Pramod,

Maybe, with my poor english , i have not been enough clear when i proposed you to move this thread towards the Transact-SQL forum and i think i have forgotten to give an important reason : the move avoids to let a thread dying because your thread is not in the most suitable forum.

Now , i have to do a merge with your thread in the Transact-SQL Forum with 2 problems : it is an operation which is not easy and the destination thread will be less readable. Only , moderators can do a move or a merge and these operations are among the most usual ( and necessary ) ones that we have to do.

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 12

Hello,

I moved this thread from the SQL Server Express Forum as i have not been able to do a merge ( invalid ID )

Is it possible for a moderator to do a merge with the thread :

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/78db1228-8c14-427c-ab5c-b7ce23b12830/#3659f63c-3d65-4c21-9532-12d491709ed2

Thanks beforehand

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 13

Thanks Papy

---

Pramod

Answer 14

Hello Naomi,

Many thanks for the merge

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.

Answer 15

hi 

here i a am facing some another problem like 

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '('.

my req is in pivot i want represent the data tabular format here my table design like 

ID int Unchecked
Name text Checked
Description text Checked
ConfigFile text Checked
AppView tinyint Checked

and my table like this way 

ID Name Description Config  appview

1 L&T EDMI ABT Meter xcv 1
2 RishM40 Rishabh Make Transducer asd 1
3 Renu Renu AI module asd 1

and my pivote like this

select 
cast(L&T as varchar(50)) as [L&T],
cast(RishM40 as varchar (50)) as [RishM40],
cast(Renu as varchar (50)) as [Renu]
from (select cast(Name as varchar(50)),cast(Description as varchar(50)) from [EMS_Network][dbo].[Devices]) q1
 pivot(sum (cast(Description as varchar(50))) for name in ([L&T],[RishM40],[Renu])) as pvt

how i can get the data from this same table .....Pramod

---

Pramod

Answer 16

Hello,

Usually the message

"Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '('"

means that you have an error an error just after a ( in line 6, maybe an incorrect name of "column"

Have a nice day

PS : i have seen , in the last line someting like from [EMS_Network][dbo].[Devices]) q1

What is q1

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.