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.
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_columnsexec(@sql)select @pivot=coalesce(@pivot+',','')+'['+pivot_column+']'from #pivot_columnsselect @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_chargedRB, VAC, 20130222, 8RB, HOL, 20131128, 8RB, VAC, 20130311, 8My output should be:emp id VAC HOLRB 16 8ThanksLado |
|
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 linkshttp://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx |
|
|
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. |
|
|
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)' |
|
|
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 |
|
|
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 |
|
|
|
|
|
|
|