0

Heres the query which is giving the issue

select * from 
(select user,logdate,[in time],[out time],[worked time] from tmp_phys_table) as sr1
PIVOT
(SUM([worked time]) FOR [LOGDATE] IN ([1])) AS TMPQ

I am unable to understand what the issue is. I was doing pivot as I wanted to make the rows into columns.

input datat table

I want the output in the below format. The image is the one which I had created in excel for the visualization of data for my report purposes,the same I want in sql,but I am not getting the idea for how to go about the dates hence the query which I had written and which is giving the above error

No column was specified for column 1 of 'sr1' for pivot function

output data

Ameya Deshpande
  • 3,580
  • 4
  • 30
  • 46
vimal vasudevan
  • 179
  • 1
  • 4
  • 17

1 Answers1

1

I think your issue is the use of a reserved word in you field name; User is a reserved word, you will need to alias it though I would highly recommend changing the field name.

SELECT * 
from (SELECT user As Usr,logdate,[in time],[out time],[worked time] 
    from tmp_phys_table) as sr1 
PIVOT (SUM([worked time]) FOR [LOGDATE] IN (1)) AS TMPQ

NOTE: I have not tested this, I am just going off what stands out as the issue

I would also strongly suggest changing some of your other columns so they do not have spaces in, as you have correctly typed they require square brackets, which will become frustrating over time.

Simon1979
  • 2,110
  • 1
  • 12
  • 15
  • Thank you @simon1979, the error is resolved but can you help me out,I dont want to ask another separate question,but i want the logdate column to be in dd-mm-yy format..basically I want the dates as the columns... – vimal vasudevan Mar 09 '15 at 06:12
  • Your issue is with the `IN ([1])` part, this should be the list of column names you want to use. See the link @Abdulla posted in his answer for a great overview of `PIVOT` by @bluefeet: http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server?lq=1 and if you still have issues getting it right post a new question with your revised code – Simon1979 Mar 09 '15 at 06:29