在SQL Server 2008中将行检索为列

Question

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

现在我希望数据显示为：

       VSKP     HYD     CHENNAI 

VSKP    0km     100km   458km   

HYD     100km     0km   200km

CHANNAI  458km  200km     0km

我尝试使用查询语句

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

点击此处下载问题的 .txt 文件

这个问题涉及到IT技术。

- Pravin Kumar

Answer 1

请尝试以下方法：

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

更新： 使用动态SQL处理可变数量的城市，如下所示：

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)

- Ravi Singh

---

是的，这是一个非常好的问题。但如果我不知道城市的数量，那么问题再次出现了。能否请@Ravi Singh给我建议？ - Pravin Kumar

@PravinKumar：您的问题不太清楚。您想知道如果城市数量是可变的，解决方案是什么吗？ - Ravi Singh

Answer 2

这里有一个解决方案（但没有使用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

翻译为：

SQL fiddle

- MikeM

---

是的，它会起作用。但是如果我不知道城市的数量...那么问题又出现了。你能建议一下吗，@MikeM？ - Pravin Kumar

Answer 3

让我通过一个简单的例子来解释这个问题。 使用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

- user1996823

---

有些类似于这篇文章。 - MikeM