Asked by:
Grid view column Formatting in vb.net
Question

User1578974752 posted
Hi
SELECT SplitBrand, SUM(Points) AS Expr1, MonthID FROM MFPMaster GROUP BY SplitBrand, MonthID by this I am getting.
splitbrand points Monthid
ABC 10 January
ABC 10 February
BBB 20 January
BBB 30 February
How can I show so that it will look like this in the asp.net form.
January February March
ABC 10 10
BBB 20 30
Thanks
Friday, January 17, 2020 1:52 AM
All replies

User288213138 posted
Hi shsu,
How can I show so that it will look like this in the asp.net formYou can try to use PIVOT method.
select * from MFPMaster select * from MFPMaster pivot ( max(points) for Monthid in([January],[February],[March]) ) as p
The result:
Best regards,
Sam
Friday, January 17, 2020 6:06 AM 
User1578974752 posted
Thanks
Actually I want to get as follows:
January February March Total Points
ABC 10 10 15 35
BBB 20 30 10 60
CCC 10 11 10 31
Total Points 40 51 35 126
There are 4 split brands.Split brands will show in one side of pivot table.
 sum(points) of January for each splitbrand(there are 4 types) and
 Points altogether(all splitbrand regardless of groupby splitbrand) for the month of January and
 Points for 4 months for all splitbrands
below is the code for each month . When I add group by in pivot table ,it is showing error.
SELECT SplitBrand, SUM(Points) AS Expr1, MonthID FROM MFPMaster GROUP BY SplitBrand, MonthID by this I am getting. Appreciate the help
Monday, January 20, 2020 2:30 AM 
User288213138 posted
Hi shsu,
shsu
January February March Total Points
ABC 10 10 15 35
BBB 20 30 10 60
CCC 10 11 10 31
Total Points 40 51 35 126
You can try below code:
select * from MFPMaster select *,([January]+[February]+[March]) as Total from MFPMaster pivot ( max(points) for Monthid in([January],[February],[March]) ) as p UNION all select 'Total Points', sum(CASE WHEN Monthid= 'January' THEN points ELSE 0 END) as 'January', sum(CASE WHEN Monthid= 'February' THEN points ELSE 0 END) as 'February', sum(CASE WHEN Monthid= 'March' THEN points ELSE 0 END) as 'March', sum(points) as 'Total' from MFPMaster
The result:
Best regards,
Sam
Monday, January 20, 2020 2:55 AM 
User1578974752 posted
Thanks. MonthID is an integer(1 to 12)
After union all showing error
Monday, January 20, 2020 6:45 AM 
User288213138 posted
Hi shsu,
Thanks. MonthID is an integer(1 to 12)You just need to change the column name.
select * from MFPMaster select *,([1]+[2]+[3]) as Total from MFPMaster pivot ( max(points) for Monthid in([1],[2],[3]) ) as p UNION all select 'Total Points', sum(CASE WHEN Monthid= '1' THEN points ELSE 0 END) as 'January', sum(CASE WHEN Monthid= '2' THEN points ELSE 0 END) as 'February', sum(CASE WHEN Monthid= '3' THEN points ELSE 0 END) as 'March', sum(points) as 'Total' from MFPMaster
Best regards,
sam
Monday, January 20, 2020 7:23 AM 
User1578974752 posted
All queries combined using UNON,INTERSECT OR EXCEPT OPERATOR must have an equal number of expression in their target lists
Above is the error showing for union
Monday, January 20, 2020 7:43 AM 
User288213138 posted
Hi shsu,
All queries combined using UNON,INTERSECT OR EXCEPT OPERATOR must have an equal number of expression in their target lists
Above is the error showing for union
Have you encountered any problems? my queries above are working properly.
Best regards,
Sam
Tuesday, January 21, 2020 3:06 AM 
User1578974752 posted
All queries combined using UNON,INTERSECT OR EXCEPT OPERATOR must have an equal number of expression in their target lists
Above is the error showing for the query that you mentioned. Thanks
Tuesday, January 21, 2020 5:42 AM 
User288213138 posted
Hi shsu,
Above is the error showing for the query that you mentionedThis is my query result:
Best regards,
sam
Tuesday, January 21, 2020 6:13 AM