Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dynamic Column from pivot function in reporting services Expand / Collapse
Author
Message
Posted Friday, February 13, 2009 4:39 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 7:54 PM
Points: 27, Visits: 404
Hi Guys,

I created a query where it contains ID, Email, Class and a dynamic column which changes or grow depending on the parameter I select.

This is my query:

DECLARE @cols NVARCHAR(2000)
SELECT @cols = COALESCE(@cols + ',[' + Session + ']',
'[' + Session + ']')
FROM TestView
WHERE Session = @Session

DECLARE @query NVARCHAR(4000)
SET @query = N'SELECT ID, Email, '+
@cols + ',class
FROM
(SELECT
ID
, Email
, Class
, Session
, Quantity
FROM
TEST
WHERE
Session IN (''' + @Session +''')
)p
PIVOT
(
SUM([Quantity])
FOR Session IN
( '+
@cols +' )
) AS pvt'

EXECUTE(@query)

The query run fine when I run it in management studio where the column header changes based on the session parameter I select. For example, If i pass thru H for session then the column returned would be ID, Email, H and class or if i pass thru A then the column heading H will change to A and the associated data.

I put this query into reporting services and created a table and drag the available field in and I lose the dynamic column field feature, can anyone assist me to fix this please.

Thanks

EK
Post #657052
Posted Thursday, April 30, 2009 3:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 8, 2009 1:40 AM
Points: 1, Visits: 53
I had similar issue..
You can create a temp table and populate the table with id,email,...
i.e...

INSERT INTO temp
exec(@query)
select * from temp
drop table temp

now the column name will be available in the Repoting service DataSet.
Post #707500
Posted Friday, October 4, 2013 5:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 31, 2014 4:49 AM
Points: 9, Visits: 41
Hi,


Saw your reply on the above post.
I have been facing similar issue.

Please help me..

As suggested by you, insert the data into temp table and then do SELECT *.

Now my problem is when i do SELECT * from temp table and export my data into some flat file i am unable to get column header.

so please tell me how to map dynamic column header with flat file export.

I believe you must be having answer of this :)


Regards,
Deepika

Post #1501529
Posted Friday, October 4, 2013 6:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:53 AM
Points: 6,917, Visits: 6,978
sun_kuang (2/13/2009)
Hi Guys,
I created a query where it contains ID, Email, Class and a dynamic column which changes or grow depending on the parameter I select.


Your query selects data using @Session and will only have one session but you use 'IN' in the query?

If the purpose is to select many sessions and pivot them then the easiest solution would be to output flat data
e.g
SELECT ID, Email, Class, Session, Quantity FROM TEST WHERE Session IN ('A','B','C')

and use a matrix in reporting services which will pivot the data and allow you to use session as column header.



Far away is close at hand in the images of elsewhere.

Anon.

Post #1501549
Posted Friday, October 4, 2013 6:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 31, 2014 4:49 AM
Points: 9, Visits: 41
Thanks David for the reply.
I am not using reporting services.

Basically i am creating pivot of my data using TSQL and trying to dump the data into flat file using BCP.

so here i am not able to fetch column headers

Regards,
Deepika
Post #1501551
Posted Friday, October 4, 2013 6:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:53 AM
Points: 6,917, Visits: 6,978
er.deepikagoyal (10/4/2013)
Thanks David for the reply.
I am not using reporting services.

Basically i am creating pivot of my data using TSQL and trying to dump the data into flat file using BCP.

so here i am not able to fetch column headers

Regards,
Deepika


No but sun_kuang is.

If you are using dynamic sql to pivot data to a table then you have to use dynamic sql to SELECT it to get column names or use 'SELECT *' (which is deemed not to be good practice)

Using complicated tsql with BCP can be troublesome as if I remember correctly BCP tries a FORMAT only attempt to get column details (not sure if this is the case) but I have encountered errors because BCP could not determine the output.

Are you able to post the query and test data?



Far away is close at hand in the images of elsewhere.

Anon.

Post #1501563
Posted Friday, October 4, 2013 7:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 31, 2014 4:49 AM
Points: 9, Visits: 41
Hi,

Yes i am able to export my data. BCP uses format to export the data using comma separated.
I have to define each column name to export the data with proper delimiter.
This is an issues since i don't know my column name.

If you have any other way around then let me know. i don't work on reporting services.
Can anything be done through TSQL or SSIS.


Regards,
Deepika Goyal





Post #1501576
Posted Friday, October 4, 2013 7:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:53 AM
Points: 6,917, Visits: 6,978
er.deepikagoyal (10/4/2013)
Hi,

Yes i am able to export my data. BCP uses format to export the data using comma separated.
I have to define each column name to export the data with proper delimiter.
This is an issues since i don't know my column name.

If you have any other way around then let me know. i don't work on reporting services.
Can anything be done through TSQL or SSIS.

Regards,
Deepika Goyal


Do you mean you want the first line of the output to be column names?

Is this the same as http://www.sqlservercentral.com/Forums/Topic1501526-391-1.aspx ?



Far away is close at hand in the images of elsewhere.

Anon.

Post #1501578
Posted Friday, October 4, 2013 7:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 31, 2014 4:49 AM
Points: 9, Visits: 41
Yes!!! Exactly :) :) :) :D

My flat file should have 1st line as column headers and rest is my data.

Post #1501579
Posted Friday, October 4, 2013 7:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 8:53 AM
Points: 6,917, Visits: 6,978
I had something similar a while ago but not dynamic columns.
In my case I was building a temp table from multiple data.
What I did was create the table with all varchar columns plus a rowid.
Insert the column names with rowid=1
Insert the data (cast/convert) with rowid=2
Select the data ordered by rowid.

If you are pivoting with dynamic columns then you will know the column names, you could use the above technique.



Far away is close at hand in the images of elsewhere.

Anon.

Post #1501586
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse