Grid view column Formatting in vb.net

Question

User-1578974752 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

Answer 1

User288213138 posted

Hi shsu,

How can I show so that it will look like this in the asp.net form

You 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

Answer 2

User-1578974752 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

Answer 3

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

Answer 4

User-1578974752 posted

Thanks. MonthID is an integer(1 to 12)

After union all showing error

Answer 5

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

Answer 6

User-1578974752 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

Answer 7

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

Answer 8

User-1578974752 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

Answer 9

User288213138 posted

Hi shsu,

Above is the error showing  for the query that you mentioned

This is my query result:

Best regards,

sam