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 Crosstab

Author  Topic 

ladowali
Starting Member

16 Posts

Posted - 2013-11-28 : 10:02:50
Hi Group,

I am trying to use one sql store procedure but don't know how to pass the parameters. I am posting here my store procedure:-

create procedure dynamic_pivot
(
@select varchar(2000),
@PivotCol varchar(100),
@Summaries varchar(100)
) as
declare @pivot varchar(max), @sql varchar(max)
select @select =replace(@select,'select ','select '+@PivotCol+' as pivot_col,')


create table #pivot_columns (pivot_column varchar(100))

Select @sql='select distinct pivot_col from ('+@select+') as t'

insert into #pivot_columns
exec(@sql)

select @pivot=coalesce(@pivot+',','')+'['+pivot_column+']'from #pivot_columns

select @sql=
'
select * from
(
'+@select+'
) as t
pivot
(
'+@Summaries+' for pivot_col in ('+@pivot+')
) as p
'
exec(@sql)

my table looks like below:
emp_id, time_code, date, time_charged
RB, VAC, 20130222, 8
RB, HOL, 20131128, 8
RB, VAC, 20130311, 8
My output should be:

emp id VAC HOL
RB 16 8


Thanks
Lado

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-11-28 : 19:45:28
i think u had refer to madhi dynamic pivot, in order for u to use


EXEC dynamic_pivot
'SELECT emp_id, time_charged FROM [put urs table name here]',
'time_code',
'SUM(time_charged)'


edit. add madhi links
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx
Go to Top of Page

ladowali
Starting Member

16 Posts

Posted - 2013-11-28 : 20:17:05
Hi waterduck,

very nice. I can at least run this procedure. as I am totally new to SQL, I am still learning new things. I have some more questions regarding this execution, what if I don't want to sum the last column and also how I can add where clause on this call to limit my records. can you just add the example similar way. for example where on the emp_id etc.

Thanks for your help.
Go to Top of Page

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-11-29 : 02:38:16
sorry late reply.

using madhi example, u can put urs [where clause] after [from].


EXEC dynamic_pivot
'SELECT emp_id, time_charged FROM [put urs table name here] WHERE emp_id = 1',
'time_code',
'SUM(time_charged)'
Go to Top of Page

ladowali
Starting Member

16 Posts

Posted - 2013-11-29 : 10:33:54
my emp_id is a character so how can I put WHERE emp_id='ABC'. it gives me incorrect syntax near 'ABC'.

Also I want to put the result set in the table so I can use that in my application.

My questions are:-

1) Can I create a view out of this store procedure for the result set?
2) Can I write the result set in the physical sql table?

Thanks
Go to Top of Page

ladowali
Starting Member

16 Posts

Posted - 2013-12-02 : 09:12:33
quote:
Originally posted by ladowali

my emp_id is a character so how can I put WHERE emp_id='ABC'. it gives me incorrect syntax near 'ABC'.

Also I want to put the result set in the table so I can use that in my application.

My questions are:-

1) Can I create a view out of this store procedure for the result set?
2) Can I write the result set in the physical sql table?

Thanks



Can anyone answer my questions.

Thanks
Go to Top of Page
   

- Advertisement -