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 Crosstab
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ladowali
Starting Member

Canada
14 Posts

Posted - 11/28/2013 :  10:02:50  Show Profile  Reply with Quote
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

Malaysia
942 Posts

Posted - 11/28/2013 :  19:45:28  Show Profile  Reply with Quote
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

Edited by - waterduck on 11/28/2013 19:46:28
Go to Top of Page

ladowali
Starting Member

Canada
14 Posts

Posted - 11/28/2013 :  20:17:05  Show Profile  Reply with Quote
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

Malaysia
942 Posts

Posted - 11/29/2013 :  02:38:16  Show Profile  Reply with Quote
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

Canada
14 Posts

Posted - 11/29/2013 :  10:33:54  Show Profile  Reply with Quote
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

Canada
14 Posts

Posted - 12/02/2013 :  09:12:33  Show Profile  Reply with Quote
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
  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.08 seconds. Powered By: Snitz Forums 2000