Dynamic Column from pivot function in reporting services

Question

Post reply

Dynamic Column from pivot function in reporting services

sun_kuang

SSChasing Mays

Points: 603
More actions
February 13, 2009 at 4:39 pm

#200268

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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

sudeshna_ju SSC Rookie Points: 45 More actions · Answer 1

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.

BI_NewBie SSCrazy Points: 2261 More actions · Answer 2

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

David Burrows SSC Guru Points: 65138 More actions · Answer 3

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.

BI_NewBie SSCrazy Points: 2261 More actions · Answer 4

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

David Burrows SSC Guru Points: 65138 More actions · Answer 5

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.

BI_NewBie SSCrazy Points: 2261 More actions · Answer 6

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

David Burrows SSC Guru Points: 65138 More actions · Answer 7

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.

BI_NewBie SSCrazy Points: 2261 More actions · Answer 8

Yes!!! Exactly 🙂 🙂 🙂 😀

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

David Burrows SSC Guru Points: 65138 More actions · Answer 9

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.