Sql pivot query into Report builder

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

Sql pivot query into Report builder

Author

Topic

sureshsmanian
Starting Member

31 Posts

Posted - 2014-10-31 : 06:58:36

Hi
I have a query which returns number of columns using pivot ( rows into columns -- dynamic sql pivot columns). Since it is dynamic pivot, how can I bind this returned values into report builder matrix reports.

Please look at this example :

First time query returns
StudentId | Col1 | Col2 | Col3

Second time query returns
StudentId | Col1 | Col2 | Col3 | Col4 | Col5 | Col6 ...

How to bind this query into report builder 3.0 reports?

Thanks for your help.
SSM

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-31 : 08:34:39

The best you can do is establish a maximum number of columns. Add missing columns to the query so the report always sees the same number of columns.

Practically speaking, there should be some limit on the width of the report. I can imagine that if you send a report with, say, 142 columns, someone will complain that it is hard to read.

sureshsmanian
Starting Member

31 Posts

Posted - 2014-11-01 : 02:15:05

Thanks for your reply.
Though I can limit the maximum number of columns, whereas the column names are getting different each time. I couldn't bind the column names since column names are generated dynamically through pivot.

Is there any other way that I could execute the pivot query and store values into an another temporary table with limited columns, later bind into the report builder.
Thanks for your help.

Regards
SSM

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-01 : 07:59:02

You can run your pivot query as an insert into... Select

sureshsmanian
Starting Member

31 Posts

Posted - 2014-11-01 : 15:40:13

Hi
Yes I have tried 'insert into' the 'select from pivot query'. But, problem is, being the dynamic number of columns returned by pivot, I couldn't write the Insert into with 'specified number of columns'..

Regards.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-01 : 16:55:44

pre-define the target table for the insert into operation with your max number of columns. Then write:

insert into predefined_table
select ...
pivot ...

That way, you are not defining the columns to be inserted into, whether 1 or 100, it should not matter.

sureshsmanian
Starting Member

31 Posts

Posted - 2014-11-03 : 11:36:14

Hi
I could do the same as per your suggestion, Insert with predefined maximum number of columns is ok, whereas what about the select pivot query, what are the fields I will select ??? since the pivot query returns the dynamic fields ..
Thanks

Regards
SSM

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-03 : 11:42:48

e.g.


create table pivot_results (pivotcol1 <type>, pivotcol2 <type>, ... pivotcoln <type>, aggrcol1 <type>, aggrcol2 <type>, ..., aggrcoln<type>)
insert into pivotresults
select pivotcol1, pivotcol2, ... pivotcoln, aggrcol1, aggrcol2, ..., aggrcolm -- note m <= n
from ...
pivot (aggr_func(aggr_col) for pivot_col in ([aggrcol1], [aggrcol2], ..., [aggrcolm])) p

You didn't post your actual table schemas or queries so that's the best I can do.

sureshsmanian
Starting Member

31 Posts

Posted - 2014-11-06 : 08:21:27

Hi
Thanks for your reply. Here my issue is simply selecting the pivot columns

Part of the query for your reference....

Insert into ttnew
SELECT EnrolmentNo,@cols from
(
Select SEA.EnrolmentNo as Enrolmentno,SDD.SubjectCode as SubjectCode,SDD.SubjectId as SubjectId from studentexamappearence SEA
) x
pivot
(
max (SubjectCode)
for SubjectId in ([@cols])
) p

@cols -- Is a memory variable which has the list of values (generated dynamically as made rows into cols), according to your suggestion m <= n wont be worked out here. Please check..

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-06 : 08:50:25

OK -- so:

1. what is the design of ttnew? Can you post the schema?
2. You need to execute the whole thing as dynamic sql, e.g.


declare @sql nvarchar(4000);
set @sql = '
insert into ttnew 
SELECT EnrolmentNo, ' + @cols + '
from
(
Select SEA.EnrolmentNo as Enrolmentno,SDD.SubjectCode as SubjectCode,SDD.SubjectId as SubjectId from studentexamappearence SEA 
) x
pivot 
(
max (SubjectCode)
for SubjectId in (' + @cols + ')
) p 
'

exec sp_executesql @sql

sureshsmanian
Starting Member

31 Posts

Posted - 2014-11-06 : 15:00:34

Hi
Thanks for your effort and time.

1) ttnew : id - autonumber, col1-nvarchar(5), col2-nvarchar(5) ....................... col20-nvarchar(5).
2) Dynamic sql : Yes I have assigned full sql into the dynamic sql query, while I am trying to insert into ttnew error populates that : number of columns mismatch between Insert and its values passed. Because, @cols, dynamic pivot columns, its varying everytime.

FYI, instead of using insert into, if I use Select ....into to ##TEMP( i hope the temporary table created at Tsql), ##TEMP storing and accepts all the dynamic columns, further i am able to display the answer on screen from ##TEMP. Whereas, now I dont know how to transfer the values from ##Temp(since, now ##TEMP has the dynamic number of columns) into a table named ttnew, which I needs to refer it at later purpose or displaying the table at report builder report.

Regards
SSM

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-06 : 15:12:37

OK -- then two choices:

1. compute and add nulls onto the end of the select after the pivoted columns to account for the number of missing colums
2. compute the insert into columns and make that part of the query dynamic as well.

sureshsmanian
Starting Member

31 Posts

Posted - 2014-11-06 : 15:27:34

Hi,
I think counting the number of pivoted columns won't be an issue. since @cols is the string, column values separated by commma. But, I dont know how to add those many remaining NULL columns dynamically in a Sql query. I would appreciate if you could provide an example in this regard. Thanks.
Regards
SSM

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-06 : 15:43:01

Well, assuming you know the number of nulls you need (should be easy), this might do it:


declare @Numnulls int = 50;
declare @nullcols nvarchar(4000);

with n0(n) as (
	select n from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n(n)),
	 n2(n) as (select 1 from n0, n0 n1),
	 n4(n) as (select 1 from n0, n0 n1),
	 n8(n) as (select 1 from n0, n0 n1),
	 N(n) as (select top(@numnulls) ROW_NUMBER() over(order by (select null)) from n8)
select @nullcols = stuff(
	(select ',null' from N	
	for xml path(''))
	,1,1,'')

select @nullcols

Note that my cte N as 10^8 rows, probably overkill for your application, so adjust accordingly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources