May 6, 2016 at 7:53 am
I have written a query using pivot and the data is correct like how I want( This excudes grouping).
Data :
id desc Color colorid
1 RoadFrames Black 2
1 Helmets Red 3
2 RoadFrames White 4
2 Helmets Yellow 5
But I have used grouping in SSRS, the problem arised when I export it as csv file, as
requested by end user, it has ignored all the grouping and the format I did like how the end user wants.
So, the output im getting is now like how it is in my query.
Now my question is how can I group the already pivoted table data and I also have to pivot a column ,
because that respective column is under column group in the SSRS.
How can I group and pivot the already pivoted data ?
The expected data should look like below:
id RoadFrames Helmets
1Black Red
2 White Yellow
Can somebody help me with this please?
Thanks in advance!
May 6, 2016 at 8:31 am
sindhupavani123 50704 (5/6/2016)
I have written a query using pivot and the data is correct like how I want( This excudes grouping).Data :
id desc Color colorid
1 RoadFrames Black 2
1 Helmets Red 3
2 RoadFrames White 4
2 Helmets Yellow 5
But I have used grouping in SSRS, the problem arised when I export it as csv file, as
requested by end user, it has ignored all the grouping and the format I did like how the end user wants.
So, the output im getting is now like how it is in my query.
Now my question is how can I group the already pivoted table data and I also have to pivot a column ,
because that respective column is under column group in the SSRS.
How can I group and pivot the already pivoted data ?
The expected data should look like below:
id RoadFrames Helmets
1Black Red
2 White Yellow
Can somebody help me with this please?
Thanks in advance!
Hi and welcome to the forums. It is best if you can provide sample data in a consumable format so others can easily work on your problem. That being said you did provide sample data and the desired output. I have provided an example of a better way to post your sample data to make it easy for the volunteers around here to help.
if OBJECT_ID('tempdb..#Something') is not null
drop table #Something
create table #Something
(
id int
, MyDesc varchar(25)
, Color varchar(10)
, ColorID int
)
insert #Something
select 1, 'RoadFrames', 'Black', 2 union all
select 1, 'Helmets', 'Red', 3 union all
select 2, 'RoadFrames', 'White', 4 union all
select 2, 'Helmets', 'Yellow', 5
Not that it is super easy to work with the same data all you need is a query to retrieve the desired output. You can easily accomplish using a crosstab. You can read more about this technique by following the links in my signature.
select id
, MAX(case when MyDesc = 'RoadFrames' then Color end) as RoadFrames
, MAX(case when MyDesc = 'Helmets' then Color end) as Helmets
from #Something
group by id
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply