SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Dynamic Pivot
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lio1972
Starting Member

19 Posts

Posted - 02/02/2014 :  10:05:06  Show Profile  Reply with Quote
I am trying to achieve a task by using the dynamic pivot, but I am confused. And I broke the whole task to small steps.
In my first small step I have an error when I am passing the variable to the subquery and I dont know why.

Declare @dates varchar(100)
SET @dates = '[2013-03-10],[2013-03-11],[2013-03-12]'

select * from (select employee,workday,hours from test)x
pivot
(
sum(hours) for workday in ( ' + @dates + ')
)p


CREATE TABLE test
 (
employee Varchar(10),
workday datetime,
hours int
)
GO
INSERT INTO test(employee,workday,hours) values('Emp1','2013-03-11',8)
INSERT INTO test(employee,workday,hours) values('Emp1','2013-03-12',4)
INSERT INTO test(employee,workday,hours) values('Emp1','2013-03-13',6)
INSERT INTO test(employee,workday,hours) values('Emp1','2013-03-14',5)
INSERT INTO test(employee,workday,hours) values('Emp2','2013-03-11',3)
INSERT INTO test(employee,workday,hours) values('Emp2','2013-03-12',8)
INSERT INTO test(employee,workday,hours) values('Emp2','2013-03-13',2)
INSERT INTO test(employee,workday,hours) values('Emp2','2013-03-14',9)
INSERT INTO test(employee,workday,hours) values('Emp3','2013-03-11',7)
INSERT INTO test(employee,workday,hours) values('Emp3','2013-03-12',8)
INSERT INTO test(employee,workday,hours) values('Emp3','2013-03-13',4)
INSERT INTO test(employee,workday,hours) values('Emp3','2013-03-14',7)

Edited by - lio1972 on 02/02/2014 19:52:34

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/02/2014 :  11:58:11  Show Profile  Reply with Quote

Declare @dates varchar(100),@SQL varchar(max)
SET @dates = '[2013-03-10],[2013-03-11],[2013-03-12]'

SET @SQL='select * from (select employee,workday,hours from test)x
pivot
(
sum(hours) for workday in ( ' + @dates + ')
)p'
EXEC (@SQL)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

lio1972
Starting Member

19 Posts

Posted - 02/02/2014 :  17:52:09  Show Profile  Reply with Quote
Thank you very much for the answer. The dates actually are results from a query how can I pass that?
I want to do something like that
Set @Dates = (select dates from test)

Do I have to iterate through the result from this query and assign it to a string or there is an easier approach?
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

277 Posts

Posted - 02/02/2014 :  18:05:49  Show Profile  Reply with Quote
quote:
Originally posted by lio1972

Thank you very much for the answer. The dates actually are results from a query how can I pass that?
I want to do something like that
Set @Dates = (select dates from test)

Do I have to iterate through the result from this query and assign it to a string or there is an easier approach?



Try this: select @Dates=stuff((select ','+dates from test for xml path('')),1,1,'')
Go to Top of Page

lio1972
Starting Member

19 Posts

Posted - 02/02/2014 :  18:29:57  Show Profile  Reply with Quote
I tried this, and this was the error:The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Declare @dates varchar(100),@SQL varchar(max)
SET @dates = stuff((select ','+ WorkDay from test for xml path('')),1,1,'')

SET @SQL='select * from (select employee,workday,hours from test)x
pivot
(
sum(hours) for workday in ( ' + @dates + ')
)p'
EXEC (@SQL)


Then I tried this

SET @dates = stuff((select ','+ Convert(Varchar(10),WorkDay,110) from test for xml path('')),1,1,'')


Incorect sintax
Go to Top of Page

lio1972
Starting Member

19 Posts

Posted - 02/02/2014 :  19:21:52  Show Profile  Reply with Quote
I also tried this ,didnt work again
declare @bigArr varchar(max)
declare @testDay varchar(20)
declare testCursor cursor for
   select distinct '[' + Convert(Varchar(12),workDay,105) +']' from test
   set @bigArr = @testDay
   OPEN testCursor
   fetch next from testCursor into @testDay
	while @@fetch_status = 0
		begin
			set @testDay = @testDay
			print @testDay
			set @bigArr = @bigArr + ',' + @testDay

			print @bigArr
			fetch next from testCursor into @testDay
		end
	close testCursor
	deallocate testCursor
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

277 Posts

Posted - 02/02/2014 :  19:22:36  Show Profile  Reply with Quote
quote:
Originally posted by lio1972

I tried this, and this was the error:The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Declare @dates varchar(100),@SQL varchar(max)
SET @dates = stuff((select ','+ WorkDay from test for xml path('')),1,1,'')

SET @SQL='select * from (select employee,workday,hours from test)x
pivot
(
sum(hours) for workday in ( ' + @dates + ')
)p'
EXEC (@SQL)


Then I tried this

SET @dates = stuff((select ','+ Convert(Varchar(10),WorkDay,110) from test for xml path('')),1,1,'')


Incorect sintax


Maybe this: SET @dates = stuff((select ',['+ Convert(Varchar(10),WorkDay,110)+']' from test for xml path('')),1,1,'')
Go to Top of Page

lio1972
Starting Member

19 Posts

Posted - 02/02/2014 :  19:34:32  Show Profile  Reply with Quote
I put the code for generating the table and the data in the first post
Thank you, but error again
Msg 105, Level 15, State 1, Line 4
Unclosed quotation mark after the character string '03-10-20)
)p'.
Declare @dates varchar(100),@SQL varchar(max)
SET @dates = stuff((select ',['+ Convert(Varchar(10),WorkDay,110)+']' from test for xml path('')),1,1,'')

SET @SQL='select * from (select employee,workday,hours from test)x
pivot
(
sum(hours) for workday in ( ' + @dates + ')
)p'
EXEC (@SQL)

Edited by - lio1972 on 02/02/2014 19:53:26
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/03/2014 :  04:14:32  Show Profile  Reply with Quote
can you do a PRINT(@SQL) instead of EXEC(@SQL) and post the result?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/03/2014 :  04:15:43  Show Profile  Reply with Quote
Also one thing you can do is increase length of variable. I've a doubt datelist is getting truncated in between

Declare @dates varchar(2000),@SQL varchar(max)
SET @dates = stuff((select ',['+ Convert(Varchar(10),WorkDay,110)+']' from test for xml path('')),1,1,'')

SET @SQL='select * from (select employee,workday,hours from test)x
pivot
(
sum(hours) for workday in ( ' + @dates + ')
)p'
EXEC (@SQL)



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

lio1972
Starting Member

19 Posts

Posted - 02/03/2014 :  06:14:25  Show Profile  Reply with Quote
Thank you guys, I had to add distinct to the query .Declare @dates varchar(200),@SQL varchar(max)
SET @dates = stuff((select distinct ',['+ Convert(Varchar(10),WorkDay,110)+']' from test for xml path('')),1,1,'')

print @dates

SET @SQL='select * from (select employee,workday,hours from test)x
pivot
(
sum(hours) for workday in ( ' + @dates + ')
)p'
exec (@SQL)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/03/2014 :  06:40:50  Show Profile  Reply with Quote
quote:
Originally posted by lio1972

Thank you guys, I had to add distinct to the query .Declare @dates varchar(200),@SQL varchar(max)
SET @dates = stuff((select distinct ',['+ Convert(Varchar(10),WorkDay,110)+']' from test for xml path('')),1,1,'')

print @dates

SET @SQL='select * from (select employee,workday,hours from test)x
pivot
(
sum(hours) for workday in ( ' + @dates + ')
)p'
exec (@SQL)



so did this work?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

lio1972
Starting Member

19 Posts

Posted - 02/04/2014 :  10:33:56  Show Profile  Reply with Quote
Yes
Thank you very much
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/10/2014 :  08:57:06  Show Profile  Reply with Quote
cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000