Grid view column Formatting in vb.net RSS

9 replies

Last post Jan 21, 2020 06:13 AM by samwu

‹ Previous Thread|Next Thread ›

Shortcuts

- Reply
shsu
Member

175 Points

372 Posts

Grid view column Formatting in vb.net

Jan 17, 2020 01:52 AM|shsu|LINK

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
- Reply
samwu
Contributor

2360 Points

948 Posts
Re: Grid view column Formatting in vb.net

Jan 17, 2020 06:06 AM|samwu|LINK
Hi shsu,

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
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
- Reply
shsu
Member

175 Points

372 Posts
Re: Grid view column Formatting in vb.net

Jan 20, 2020 02:30 AM|shsu|LINK
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
- Reply
samwu
Contributor

2360 Points

948 Posts
Re: Grid view column Formatting in vb.net

Jan 20, 2020 02:55 AM|samwu|LINK
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
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
- Reply
shsu
Member

175 Points

372 Posts

Re: Grid view column Formatting in vb.net

Jan 20, 2020 06:45 AM|shsu|LINK

Thanks. MonthID is an integer(1 to 12)

After union all showing error
- Reply
samwu
Contributor

2360 Points

948 Posts
Re: Grid view column Formatting in vb.net

Jan 20, 2020 07:23 AM|samwu|LINK
Hi shsu,

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
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
- Reply
shsu
Member

175 Points

372 Posts

Re: Grid view column Formatting in vb.net

Jan 20, 2020 07:43 AM|shsu|LINK

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
- Reply
samwu
Contributor

2360 Points

948 Posts

Re: Grid view column Formatting in vb.net

Jan 21, 2020 03:06 AM|samwu|LINK

Hi shsu,

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

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
- Reply
shsu
Member

175 Points

372 Posts

Re: Grid view column Formatting in vb.net

Jan 21, 2020 05:42 AM|shsu|LINK

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
- Reply
samwu
Contributor

2360 Points

948 Posts

Re: Grid view column Formatting in vb.net

Jan 21, 2020 06:13 AM|samwu|LINK

Hi shsu,

shsu
Above is the error showing for the query that you mentioned

This is my query result:

Best regards,

sam

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.