使用3个表将行作为列返回的SQL查询 [英] SQL query for returning rows as column using 3 tables

问题描述

大家好，

我有3个表，即投诉，资产类型，学校

我的投诉表如下:

投诉编号SchoolId AssetTypeId
1 1 1
2 1 2
3 1 3
4 2 1
5 2 3
6 1 2

AssetType表:

AssetTypeId AssetType
1硬件
2 SW
3个LMS

课桌:
SchoolId SchoolName
1 XYZ
2 ABC

现在我想运行一个查询，该查询给我如下结果:

SchoolName HW SW LMS
XYZ 1 2 1
ABC 0 1 1

基本上我想要AssetType表的数据透视表，并希望基于学校名称的投诉计数

请帮助我如何编写此查询并获得所需的输出，我尝试了很多，但未成功.

我尝试过这个东西:
1.我创建了一个临时表，其中的列是动态创建的，这些列的名称是资产类型表中的行:

查询是

Hello everyone,

I have 3 tables namely Complaints,AssetType,Schools

My Complaints Table is as follow:

ComplaintId SchoolId AssetTypeId
1 1 1
2 1 2
3 1 3
4 2 1
5 2 3
6 1 2

AssetType Table :

AssetTypeId AssetType
1 HW
2 SW
3 LMS

School Table :
SchoolId SchoolName
1 XYZ
2 ABC

Now i want to run a query which gives me result as follows :

SchoolName HW SW LMS
XYZ 1 2 1
ABC 0 1 1

Basically i want pivot of AssetType table and want the count of complaints based on school name

Please help me in how to write this query and get the desired output, i tried a lot but was unsuccessful.

I tried this thing :
1. I created a temporary table in which columns are created dynamically , the names of the columns were the rows in the assettype table :

query was

declare @columnname nvarchar(max)
declare @firstcolumn nvarchar(max)
select @firstcolumn =Assettype from [mh.AssetType_Master] where AssetTYpeid=1 and isdeleted=''false'' order by AssetType 
Declare @sql3 varchar (1000)
set @sql3=''Create table temp (id int identity (1,1),[''+@firstcolumn+''] varchar (max))''
exec(@sql3)

declare column_cursor cursor for
select distinct AssetType from [mh.assetType_Master] where isdeleted=''false''

open column_cursor
fetch next from column_cursor into @columnname

while @@FETCH_STATUS =0
begin
	if(@columnname !=@firstcolumn )
	begin
		declare @sql nvarchar(max)
		set @sql='' alter table temp add [''+@columnname+''] varchar (max) default null''
		exec(@sql)	
	end
	fetch next from column_cursor into @columnname
end
close column_cursor
deallocate column_cursor
--select * from temp
--drop table temp

Step 2 :

Then i have declared a cursor for schools

declare @schoolname nvarchar(max)
declare @schoolId int
--declare @assettypeid int

declare school_cursor cursor for
select schoolid,schoolname from School where isdeleted=''false''
open school_cursor
fetch next from school_cursor into @schoolid,@schoolname
while @@fetchstatus=0
begin
////// Here was my problem.... where i was stuck up.....
end

问候，
Krunal

Regards,
Krunal

推荐答案

您可以对AssetTypes进行硬编码吗?通常，Pivot的工作方式与tat ..类似.以下示例的工作方式相同.

Could you hardcode the AssetTypes? Normally Pivot works like tat.. the following example works the same way..

select * from 
(
select S.SchoolName, A.AssetType, S.SchooldId
from Complaints C inner join AssetType A on A.AssetTypeId = C.AssetID inner join school S on S.SchooldId = C.SchoolID
) DataTable
Pivot
(
count(SchooldId)
for AssetType
in ([HW],[SW],[LMS])
)
PivotTable

这是通用的..不涉及硬编码

Here is the Generic one.. no hardcoding involvded

DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders = 
  COALESCE(
    @PivotColumnHeaders + ',[' + cast(AssetType as varchar) + ']',
    '[' + cast(AssetType as varchar)+ ']'
  )
FROM AssetType A
DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
select * from 
(
select S.SchoolName, A.AssetType, S.SchooldId
from Complaints C inner join AssetType A on A.AssetTypeId = C.AssetID inner join school S on S.SchooldId = C.SchoolID
) PivotData
Pivot
(
  COUNT(SchooldId)
    FOR AssetType IN (
      ' + @PivotColumnHeaders + '
    )
  ) AS PivotTable
'
EXECUTE(@PivotTableSQL)

这篇关于使用3个表将行作为列返回的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！