CODEWITHSUNDEEP
sqlsql-serverpivot
awais
awais

Reputation: 515

SQL Query to show data in the form of Matrix

I'm using the following SQL query to fetch the records.

I want to achieve the top 10 most flown origins and destination and their count respectively. 

SELECT TOP 10 dep.ICAO 'Origin', arr.ICAO 'Destination', COUNT(dep.Id) AS Total
FROM TripSectors ts
JOIN Airports dep ON ts.DepartureAirportId = dep.Id
LEFT JOIN Airports arr on arr.Id = ts.ArrivalAirportId
WHERE ts.ArrivalAirportId IS NOT NULL
GROUP BY dep.ICAO, arr.ICAO
ORDER BY Total  DESC

RESULT

Origin  Destination Total
KHND    KSDL    8541
KSNA    KHND    8359
KHND    KPSP    8275
KPSP    KHND    8267
KHND    KCRQ    8266
KCNO    KHND    8250
KHND    KCNO    8237
KHND    KTUS    8237
KHND    KAPC    8215
KCVO    KHND    8183

I need my data in this form like MATRIX because of origin and destination total is kept changing. I want something like dynamic headers for the destination. 

Origin  KSDL    KPSP    KCRQ    KCNO    KTUS   KAPC
KHND    8541    8275    8266    8237    8237   8215

I tried this but not getting required result


DECLARE @SQLQUERY  AS NVARCHAR(MAX)
DECLARE @PivotColumns AS NVARCHAR(MAX)

SELECT distinct top 10 @PivotColumns = COALESCE(@PivotColumns + ',', '') + QUOTENAME(arr.ICAO)
FROM TripSectors ts
JOIN Airports arr on arr.Id = ts.ArrivalAirportId
WHERE ts.ArrivalAirportId IS NOT NULL
GROUP BY arr.ICAO

--SELECT @PivotColumns

SET @SQLQUERY = N'WITH Locations AS (
   SELECT
      d.ICAO As Origin,
      d.Id AS OriginId
   FROM
      dbo.TripSectors S
      INNER JOIN dbo.Airports d ON S.DepartureAirportId = d.Id)

SELECT *, '+ @PivotColumns +'
FROM Locations
PIVOT (Count(OriginId) FOR Origin IN ('+ @PivotColumns +')) P
';

EXEC sp_executesql @SQLQUERY

Upvotes: 0

Views: 207

Answers (1)

KumarHarsh
KumarHarsh

Reputation: 5094

Put your require Resultset in #temp table. Dynamic Sql should be as short as possible.It is easy to debug.

create table #temp(Origin varchar(30),Destination varchar(30), Total int)
insert into #temp
values
 ('KHND','KSDL',8541)
,('KSNA','KHND',8359)
,('KHND','KPSP',8275)
,('KPSP','KHND',8267)
,('KHND','KCRQ',8266)
,('KCNO','KHND',8250)
,('KHND','KCNO',8237)
,('KHND','KTUS',8237)
,('KHND','KAPC',8215)
,('KCVO','KHND',8183)

declare @PvtCol varchar(500)=''

declare @Sql nvarchar(500);

--select * from #temp

;With CTE as
(
select  Destination,total
,row_number()over(partition by Destination  order by total desc)rn 
from #temp

)
SELECT 
@PvtCol = COALESCE(@PvtCol + ', ' + QUOTENAME(Destination), QUOTENAME(Destination))   
FROM cte
where rn=1
--and Destination<>'KHND'
order by total desc


SET @PvtCol = STUFF(@PvtCol, 1, 1, '');

SELECT @PvtCol;


set @Sql='select Origin,'+@PvtCol+' from
       (select Origin,Destination,Total from #temp /*where origin=''KHND''*/) as src
       pivot(max(total) for Destination in( '+@PvtCol+')) as pvt';

print @Sql
execute sp_executesql @Sql


drop table #temp

if you are looking for only individual origin then you can uncomment where origin=''KHND'' and --and Destination<>'KHND'

Upvotes: 1

Related Questions