SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamic Column from pivot function in reporting services


Dynamic Column from pivot function in reporting services

Author
Message
sun_kuang
sun_kuang
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 405
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
sudeshna_ju
sudeshna_ju
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 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.
BI_NewBie
BI_NewBie
SSChasing Mays
SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)

Group: General Forum Members
Points: 632 Visits: 157
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 Smile


Regards,
Deepika
David Burrows
David Burrows
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16824 Visits: 10127
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
BI_NewBie
SSChasing Mays
SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)

Group: General Forum Members
Points: 632 Visits: 157
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
David Burrows
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16824 Visits: 10127
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
BI_NewBie
SSChasing Mays
SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)

Group: General Forum Members
Points: 632 Visits: 157
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
David Burrows
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16824 Visits: 10127
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
BI_NewBie
SSChasing Mays
SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)SSChasing Mays (632 reputation)

Group: General Forum Members
Points: 632 Visits: 157
Yes!!! Exactly Smile Smile Smile BigGrin

My flat file should have 1st line as column headers and rest is my data.
David Burrows
David Burrows
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16824 Visits: 10127
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.


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search