TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
ahmed elbarbary
NA
1.6k
274.6k
How to call sp and pass parameters to make pivot on sample below?
Jan 7 2021 4:17 PM
I can't call sp and how to pass parameter to make pivot to temp table below
so can you help me execute this sp and get result
stored procedure name [dbo].[rs_pivot_table]
Create
Procedure
[dbo].[rs_pivot_table]
@
schema
sysname=dbo,
@
table
sysname,
@
column
sysname,
@agg nvarchar(
max
),
@sel_cols
varchar
(
max
),
@new_table sysname,
@add_to_col_name sysname=
null
As
--Exec dbo.rs_pivot_table dbo,##TEMPORAL1,tip_liq,'sum([val_liq]),sum([can_liq]),','cod_emp,cod_con,tip_liq',##TEMPORAL1PVT,'hola';
Begin
Declare
@query
varchar
(
max
)=
''
;
Declare
@aggDet
varchar
(100);
Declare
@opp_agg
varchar
(5);
Declare
@col_agg
varchar
(100);
Declare
@pivot_col sysname;
Declare
@query_col_pvt
varchar
(
max
)=
''
;
Declare
@full_query_pivot
varchar
(
max
)=
''
;
Declare
@ind_tmpTbl
int
;
--Indicador de tabla temporal 1=tabla temporal global 0=Tabla fisica
Create
Table
#pvt_column(
pivot_col
varchar
(100)
);
Declare
@column_agg
table
(
opp_agg
varchar
(5),
col_agg
varchar
(100)
);
IF EXISTS (
SELECT
*
FROM
sys.objects
WHERE
object_id = OBJECT_ID(@
table
)
AND
type
in
(N
'U'
))
Set
@ind_tmpTbl=0;
ELSE
IF OBJECT_ID(
'tempdb..'
+ltrim(rtrim(@
table
)))
IS
NOT
NULL
Set
@ind_tmpTbl=1;
IF EXISTS (
SELECT
*
FROM
sys.objects
WHERE
object_id = OBJECT_ID(@new_table)
AND
type
in
(N
'U'
))
OR
OBJECT_ID(
'tempdb..'
+ltrim(rtrim(@new_table)))
IS
NOT
NULL
Begin
Set
@query=
'DROP TABLE '
+@new_table+
''
;
Exec
(@query);
End
;
Select
@query=
'Select distinct '
+@
column
+
' From '
+(
case
when
@ind_tmpTbl=1
then
'tempdb.'
else
''
end
)+@
schema
+
'.'
+@
table
+
' where '
+@
column
+
' is not null;'
;
Print @query;
Insert
into
#pvt_column(pivot_col)
Exec
(@query)
While charindex(
','
,@agg,1)>0
Begin
Select
@aggDet=
Substring
(@agg,1,charindex(
','
,@agg,1)-1);
Insert
Into
@column_agg(opp_agg,col_agg)
Values
(
substring
(@aggDet,1,charindex(
'('
,@aggDet,1)-1),ltrim(rtrim(
replace
(
substring
(@aggDet,charindex(
'['
,@aggDet,1),charindex(
']'
,@aggDet,1)-4),
')'
,
''
))));
Set
@agg=
Substring
(@agg,charindex(
','
,@agg,1)+1,len(@agg))
End
Declare
cur_agg
cursor
read_only forward_only
local
static
for
Select
opp_agg,col_agg
from
@column_agg;
Open
cur_agg;
Fetch
Next
From
cur_agg
Into
@opp_agg,@col_agg;
While @@fetch_status=0
Begin
Declare
cur_col
cursor
read_only forward_only
local
static
for
Select
pivot_col
From
#pvt_column;
Open
cur_col;
Fetch
Next
From
cur_col
Into
@pivot_col;
While @@fetch_status=0
Begin
Select
@query_col_pvt=
'isnull('
+@opp_agg+
'(case when '
+@
column
+
'='
+quotename(@pivot_col,
char
(39))+
' then '
+@col_agg+
' else null end),0) as ['
+
lower
(
Replace
(
Replace
(@opp_agg+
'_'
+
convert
(
varchar
(100),@pivot_col)+
'_'
+
replace
(
replace
(@col_agg,
'['
,
''
),
']'
,
''
),
' '
,
''
),
'&'
,
''
))+
(
case
when
@add_to_col_name
is
null
then
space
(0)
else
'_'
+
isnull
(ltrim(rtrim(@add_to_col_name)),
''
)
end
)+
']'
print @query_col_pvt
Select
@full_query_pivot=@full_query_pivot+@query_col_pvt+
', '
--print @full_query_pivot
Fetch
Next
From
cur_col
Into
@pivot_col;
End
Close
cur_col;
Deallocate
cur_col;
Fetch
Next
From
cur_agg
Into
@opp_agg,@col_agg;
End
Close
cur_agg;
Deallocate
cur_agg;
Select
@full_query_pivot=
substring
(@full_query_pivot,1,len(@full_query_pivot)-1);
Select
@query=
'Select '
+@sel_cols+
','
+@full_query_pivot+
' into '
+@new_table+
' From '
+(
case
when
@ind_tmpTbl=1
then
'tempdb.'
else
''
end
)+
@
schema
+
'.'
+@
table
+
' Group by '
+@sel_cols+
';'
;
print @query;
Exec
(@query);
End
;
GO
CREATE
TABLE
#yt
(
[Store]
int
,
[Week]
int
,
[xCount]
int
);
INSERT
INTO
#yt
(
[Store],
[Week], [xCount]
)
VALUES
(102, 1, 96),
(101, 1, 138),
(105, 1, 37),
(109, 1, 59),
(101, 2, 282),
(102, 2, 212),
(105, 2, 78),
(109, 2, 97),
(105, 3, 60),
(102, 3, 123),
(101, 3, 220),
(109, 3, 87);
Expected result as below
Store 1 2 3 4 5 6....
-----
101 138 282 220
102 96 212 123
105 37
109
Reply
Answers (
4
)
what's wrong with query ?
Database design problem