Retrieve Rows as columns in sqlserver 2008

问题:

i have a Table DistanceTable

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 be displayed as

       VSKP     HYD     CHENNAI 

VSKP    0km     100km   458km   

HYD     100km     0km   200km

CHANNAI  458km  200km     0km

I tried with the query

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

Link To the question in .txt file

回答1:

Try this :

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 variable number of cities as 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)

回答2:

here's a 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 fiddle

回答3:

Let me explain this issue demonstrating 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