I have 3 tables in my database. tblAssets contains all assets, tbl_Projects contains all locations and tblLocationAssets contains the location and asset_id and asset quantity on that location.
select asset_id, asset_name from tblAssets order by asset_id
select asset_id, asset_name , location_id ,quantity from tblLocationAssets order by asset_id
select Sno, Location from tbl_Projects order by Sno
The output as below
I want a result set like below i.e. All locations should come as columns (either the location contains any assets or not) and
all assets come as rows (either any location contains that asset or not)
Varanasi
Dehri-On- Son
Garhshankar
Azamgarh
1
Safety Instruction Board
2 (quantity)
0 (quantity)
2 (quantity)
1 (quantity)
2
White Helmet
1 (quantity)
2 (quantity)
3 (quantity)
0 (quantity)
3
Safety Shoes
3 (quantity)
5 (quantity)
4 (quantity)
7 (quantity)
4
Radium Jacket Green
0 (quantity)
1 (quantity)
0 (quantity)
0 (quantity)
Please suggest.
It is our choices, that show what we truly are, far more than our abilities.
As far as I think,you need to join three tables and sum quantity.Just like this:
Table Asset:
Table Location:
Table LocationAsset:
Sql query:
select * from
(select a.asset_id, a.asset_name,b.Location,la.quantity
from LocationAssets la
inner join Asset a on a.asset_id=la.asset_id
inner join Location b on b.Sno=la.Location_id) AS t
PIVOT(
Sum(quantity)
for Location in( Varanasi,Dehri,Garhshankar,Azamgarh)
) AS Tab2
Result:
Best regards,
Yijing Sun
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.
select * from
(select a.asset_id, a.asset_name,b.Location,la.quantity
from LocationAssets la
inner join Asset a on a.asset_id=la.asset_id
inner join Location b on b.Sno=la.Location_id) AS t
PIVOT(
Sum(quantity)
for Location in( Varanasi,Dehri,Garhshankar,Azamgarh)
) AS Tab2
select * from
(select a.asset_id, a.asset_name,b.Location,la.quantity
from tblLocationAssets la
inner join tblAssets a on a.asset_id=la.asset_id
inner join tbl_Projects b on b.Sno=la.Location_id) AS t
PIVOT(
Sum(quantity)
for Location in( Varanasi,Dehri,Garhshankar,Azamgarh)
) AS Tab2
There are 51 locations on my table. I need the result for all, either location has an asset or not. I think it should be dynamic.
Currently, it shows error.
Msg 8117, Level 16, State 1, Line 1
Operand data type nvarchar is invalid for sum operator.
It is our choices, that show what we truly are, far more than our abilities.
Msg 8117, Level 16, State 1, Line 1
Operand data type nvarchar is invalid for sum operator.
The SUM() function returns the total sum of a numeric column.I have used
int of quantity.You couldn't use nvarchar.
Best regards,
Yijing Sun
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.
--===== Local variables to hold the 3 different sections of Dynamic SQL.
DECLARE @SQL1 VARCHAR(MAX) -- First part, which is static
,@SQL2 VARCHAR(MAX) -- Second part, which is the dynamic column list of the CROSSTAB
,@SQL3 VARCHAR(MAX) -- Third part, which is also static
;
--===== First part, which is static
SELECT @SQL1 = '
WITH CTEAssets AS
(
SELECT asset_id, location_id, quantity = SUM(quantity)
FROM dbo.tblLocationAssets
GROUP BY asset_id, location_id
)
SELECT Location = TP.location
'
;
--===== Second part, which is the dynamic column list of the CROSSTAB
SELECT @SQL2 = ISNULL(@SQL2,'')+REPLACE(REPLACE('
,[<<asset_name>>] = SUM(CASE WHEN cteAsst.asset_id = <<asset_id>> THEN cteAsst.quantity ELSE 0 END)'
,'<<asset_name>>',RTRIM([asset_name])) --This is where the other end of the 2 REPLACEs start
,'<<asset_id>>',CONVERT(VARCHAR(10),asset_id))
FROM dbo.tblAssets
ORDER BY [asset_id] --This could cause a problem with building @SQL2 but I had no data to check with.
;
--===== Third part, which is also static.
-- Note that we don't need a join to the tbl_projects table because
-- that's were we got the info from for the Dynamic SQL above.
SELECT @SQL3 = '
FROM dbo.tbl_Projects TP
LEFT JOIN CTEAssets cteAsst ON TP.Sno = cteAsst.location_id
group by TP.Location , TP.Sno
order by TP.Sno DESC
;'
;
--===== Combine the 3 parts of the Dynamic SQL and execute it all as a single query.
exec (@SQL1+@SQL2+@SQL3)
;
The above was the dynamic sql to achieve the requirement I shared. It is working perfectly.
It is our choices, that show what we truly are, far more than our abilities.
Participant
1441 Points
2819 Posts
how to get Pivot result set using 3 tables
Sep 01, 2020 04:54 PM|demoninside9|LINK
Hello, Everyone,
I have 3 tables in my database. tblAssets contains all assets, tbl_Projects contains all locations and tblLocationAssets contains the location and asset_id and asset quantity on that location.
The output as below
I want a result set like below i.e. All locations should come as columns (either the location contains any assets or not) and all assets come as rows (either any location contains that asset or not)
Please suggest.
Contributor
2210 Points
906 Posts
Re: how to get Pivot result set using 3 tables
Sep 02, 2020 05:34 AM|yij sun|LINK
Hi demoninside9,
As far as I think,you need to join three tables and sum quantity.Just like this:
Table Asset:
Table Location:
Table LocationAsset:
Sql query:
Result:
Best regards,
Yijing Sun
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.
Participant
1441 Points
2819 Posts
Re: how to get Pivot result set using 3 tables
Sep 02, 2020 05:50 AM|demoninside9|LINK
There are 51 locations on my table. I need the result for all, either location has an asset or not. I think it should be dynamic.
Currently, it shows error.
Msg 8117, Level 16, State 1, Line 1
Operand data type nvarchar is invalid for sum operator.
Contributor
2210 Points
906 Posts
Re: how to get Pivot result set using 3 tables
Sep 02, 2020 06:39 AM|yij sun|LINK
Hi demoninside9,
The SUM() function returns the total sum of a numeric column.I have used int of quantity.You couldn't use nvarchar.
Best regards,
Yijing Sun
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.
Participant
1441 Points
2819 Posts
Re: how to get Pivot result set using 3 tables
Sep 16, 2020 10:22 AM|demoninside9|LINK
--===== Local variables to hold the 3 different sections of Dynamic SQL. DECLARE @SQL1 VARCHAR(MAX) -- First part, which is static ,@SQL2 VARCHAR(MAX) -- Second part, which is the dynamic column list of the CROSSTAB ,@SQL3 VARCHAR(MAX) -- Third part, which is also static ; --===== First part, which is static SELECT @SQL1 = ' WITH CTEAssets AS ( SELECT asset_id, location_id, quantity = SUM(quantity) FROM dbo.tblLocationAssets GROUP BY asset_id, location_id ) SELECT Location = TP.location ' ; --===== Second part, which is the dynamic column list of the CROSSTAB SELECT @SQL2 = ISNULL(@SQL2,'')+REPLACE(REPLACE(' ,[<<asset_name>>] = SUM(CASE WHEN cteAsst.asset_id = <<asset_id>> THEN cteAsst.quantity ELSE 0 END)' ,'<<asset_name>>',RTRIM([asset_name])) --This is where the other end of the 2 REPLACEs start ,'<<asset_id>>',CONVERT(VARCHAR(10),asset_id)) FROM dbo.tblAssets ORDER BY [asset_id] --This could cause a problem with building @SQL2 but I had no data to check with. ; --===== Third part, which is also static. -- Note that we don't need a join to the tbl_projects table because -- that's were we got the info from for the Dynamic SQL above. SELECT @SQL3 = ' FROM dbo.tbl_Projects TP LEFT JOIN CTEAssets cteAsst ON TP.Sno = cteAsst.location_id group by TP.Location , TP.Sno order by TP.Sno DESC ;' ; --===== Combine the 3 parts of the Dynamic SQL and execute it all as a single query. exec (@SQL1+@SQL2+@SQL3) ;The above was the dynamic sql to achieve the requirement I shared. It is working perfectly.