Get rows as columns in sqlserver 2008

Question

I have a DistanceTable table

It has columns like

Slno From To Dist ----------------------------- 1 Vskp Hyd 100 2 Hyd Chennai 200 3 Chennai Vskp 458 

Now I want the data to display as

  VSKP HYD CHENNAI VSKP 0km 100km 458km HYD 100km 0km 200km CHANNAI 458km 200km 0km 

I tried with the request

 SELECT * FROM ( SELECT slno, fromcity ,tocity FROM DistanceTable ) AS ET PIVOT(MIN(slno) FOR fromcity IN (tocity) AS PT 

Link to a question in a .txt file

Answer 1

Try the following:

 SELECT fromcity ,[Vskp]=isnull([Vskp],0) ,[Hyd]=isnull([Hyd],0) ,[Chennai]=isnull([Chennai],0) FROM ( SELECT fromcity ,tocity,Dist FROM DistanceTable union SELECT tocity,fromcity ,Dist FROM DistanceTable ) AS ET PIVOT (max(dist) FOR tocity IN ([Vskp],[Hyd],[Chennai]) ) AS PT 

SQL FIddle

Update: Use dynamic SQL for a variable number of cities, as shown below:

 DECLARE @cols VARCHAR(MAX), @IsNullCols VARCHAR(MAX), @query VARCHAR(MAX) SELECT @cols = STUFF(( SELECT DISTINCT ', [' + tocity + ']' FROM (SELECT tocity FROM DistanceTable union SELECT fromcity FROM DistanceTable)a FOR XML PATH('') ), 1, 2, ''); print @cols; SELECT @IsNullCols = STUFF(( SELECT DISTINCT ', ['+tocity+']=IsNull([' + tocity + '],0)' FROM (SELECT tocity FROM DistanceTable union SELECT fromcity FROM DistanceTable)a FOR XML PATH('') ), 1, 2, ''); print @IsNullCols; set @query = 'SELECT fromcity ,'+ @IsNullCols +' FROM ( SELECT fromcity ,tocity,Dist FROM DistanceTable union SELECT tocity,fromcity ,Dist FROM DistanceTable ) AS ET PIVOT (max(dist) FOR tocity IN (' +@cols +') ) AS PT ' exec(@query) 

Answer 2

here's the solution (but w / out pivot):

 SELECT fromcity as ' ' ,ISNULL((SELECT SUM(dist) FROM DistanceTable d2 WHERE (d2.tocity = 'VSKP' AND d2.fromcity = d.fromcity) OR (d2.tocity = d.fromcity AND d2.fromcity = 'VSKP')), 0) as 'VSKP' ,ISNULL((SELECT SUM(dist) FROM DistanceTable d2 WHERE (d2.tocity = 'HYD' AND d2.fromcity = d.fromcity) OR (d2.tocity = d.fromcity AND d2.fromcity = 'HYD')), 0) as 'HYD' ,ISNULL((SELECT SUM(dist) FROM DistanceTable d2 WHERE (d2.tocity = 'CHENNAI' AND d2.fromcity = d.fromcity) OR (d2.tocity = d.fromcity AND d2.fromcity = 'CHENNAI')), 0) as 'CHENNAI' FROM DistanceTable d 

SQL script

Answer 3

Let me explain this problem with a simple example. USE AdventureWorks

 GO -- Creating Test Table CREATE TABLE Product(Cust VARCHAR(25), Product VARCHAR(20), QTY INT) GO -- Inserting Data into Table INSERT INTO Product(Cust, Product, QTY) VALUES('KATE','VEG',2) INSERT INTO Product(Cust, Product, QTY) VALUES('KATE','SODA',6) INSERT INTO Product(Cust, Product, QTY) VALUES('KATE','MILK',1) INSERT INTO Product(Cust, Product, QTY) VALUES('KATE','BEER',12) INSERT INTO Product(Cust, Product, QTY) VALUES('FRED','MILK',3) INSERT INTO Product(Cust, Product, QTY) VALUES('FRED','BEER',24) INSERT INTO Product(Cust, Product, QTY) VALUES('KATE','VEG',3) GO -- Selecting and checking entires in table SELECT * FROM Product GO -- Pivot Table ordered by PRODUCT SELECT PRODUCT, FRED, KATE FROM ( SELECT CUST, PRODUCT, QTY FROM Product) up PIVOT (SUM(QTY) FOR CUST IN (FRED, KATE)) AS pvt ORDER BY PRODUCT GO -- Pivot Table ordered by CUST SELECT CUST, VEG, SODA, MILK, BEER, CHIPS FROM ( SELECT CUST, PRODUCT, QTY FROM Product) up PIVOT (SUM(QTY) FOR PRODUCT IN (VEG, SODA, MILK, BEER, CHIPS)) AS pvt ORDER BY CUST GO