Dynamic Pivot - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dynamic Pivot

Author

Topic

lio1972
Starting Member

19 Posts

Posted - 2014-02-02 : 10:05:06

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)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-02 : 11:58:11

[code]
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)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

lio1972
Starting Member

19 Posts

Posted - 2014-02-02 : 17:52:09

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?

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-02-02 : 18:05:49

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,'')

lio1972
Starting Member

19 Posts

Posted - 2014-02-02 : 18:29:57

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

lio1972
Starting Member

19 Posts

Posted - 2014-02-02 : 19:21:52

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

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-02-02 : 19:22:36

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,'')

lio1972
Starting Member

19 Posts

Posted - 2014-02-02 : 19:34:32

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)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-03 : 04:14:32

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-03 : 04:15:43

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

lio1972
Starting Member

19 Posts

Posted - 2014-02-03 : 06:14:25

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)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-03 : 06:40:50

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

lio1972
Starting Member

19 Posts

Posted - 2014-02-04 : 10:33:56

Yes
Thank you very much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-10 : 08:57:06

cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources