select * from MFPMaster
select * from MFPMaster
pivot
(
max(points)
for Monthid in([January],[February],[March])
)
as p
The result:
Best regards,
Sam
IIS.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today. Learn more >
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
IIS.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today. Learn more >
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
IIS.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today. Learn more >
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
IIS.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today. Learn more >
Above is the error showing for the query that you mentioned
This is my query result:
Best regards,
sam
IIS.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today. Learn more >
Member
186 Points
406 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
Contributor
3370 Points
1409 Posts
Re: Grid view column Formatting in vb.net
Jan 17, 2020 06:06 AM|samwu|LINK
Hi shsu,
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
Member
186 Points
406 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.
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
Contributor
3370 Points
1409 Posts
Re: Grid view column Formatting in vb.net
Jan 20, 2020 02:55 AM|samwu|LINK
Hi shsu,
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
Member
186 Points
406 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
Contributor
3370 Points
1409 Posts
Re: Grid view column Formatting in vb.net
Jan 20, 2020 07:23 AM|samwu|LINK
Hi shsu,
You just need to change the column name.
Best regards,
sam
Member
186 Points
406 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
Contributor
3370 Points
1409 Posts
Re: Grid view column Formatting in vb.net
Jan 21, 2020 03:06 AM|samwu|LINK
Hi shsu,
Have you encountered any problems? my queries above are working properly.
Best regards,
Sam
Member
186 Points
406 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
Contributor
3370 Points
1409 Posts
Re: Grid view column Formatting in vb.net
Jan 21, 2020 06:13 AM|samwu|LINK
Hi shsu,
This is my query result:
Best regards,
sam