求一个行列转换的语句，可能稍微复杂点，就不会转了，哈哈

Question

ID Product Supplier1 Supplier2 Supplier3 City1 City2 City3
1 Car Honda Toyota Nissan Detroit Miami Los Angeles
2 Bike Schwinn Roadmaster Fleetwing Cincinatti Chicago Tampa
3 Motorcycle Harley Yamaha Kawasaki Omaha Dallas Atlanta
结果Id Product SuppID SupplierName CityName
1 Car 1 Honda Detroit
1 Car 2 Nissan Los Angeles
1 Car 3 Toyota Miami
2 Bike 1 Fleetwing Tampa
2 Bike 2 Roadmaster Chicago
2 Bike 3 Schwinn Cincinatti
3 Motorcycle 1 Harley Omaha
3 Motorcycle 2 Kawasaki Atlanta
3 Motorcycle 3 Yamaha Dallas

Answer 1

IF OBJECT_ID('tempdb..#Suppliers' , 'U') IS NOT NULL 
  DROP TABLE #Suppliers
CREATE TABLE #Suppliers
  (
   ID INT ,
   Product VARCHAR(500) ,
   Supplier1 VARCHAR(500) ,
   Supplier2 VARCHAR(500) ,
   Supplier3 VARCHAR(500) ,
   City1 VARCHAR(500) ,
   City2 VARCHAR(500) ,
   City3 VARCHAR(500)
  )
INSERT  INTO #Suppliers
        SELECT  1 , 'Car' , 'Honda' , 'Toyota' , 'Nissan' , 'Detroit' , 'Miami' , 'Los Angeles'
        UNION ALL
        SELECT  2 , 'Bike' , 'Schwinn' , 'Roadmaster' , 'Fleetwing' , 'Cincinatti' , 'Chicago' , 'Tampa'
        UNION ALL
        SELECT  3 , 'Motorcycle' , 'Harley' , 'Yamaha' , 'Kawasaki' , 'Omaha' , 'Dallas' , 'Atlanta' --SELECT * FROM #Suppliers
 
 
 SELECT ID , Product , SuppID = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY SupplierName) , SupplierName , CityName
 FROM   #Suppliers
 CROSS APPLY ( VALUES ( Supplier1, City1)
    , ( Supplier2, City2)
    , ( Supplier3, City3) ) x (SupplierName , CityName)
 WHERE  SupplierName IS NOT NULL
        OR CityName IS NOT NULL
/*
ID Product SuppID SupplierName CityName
1 Car 1 Honda Detroit
1 Car 2 Nissan Los Angeles
1 Car 3 Toyota Miami
2 Bike 1 Fleetwing Tampa
2 Bike 2 Roadmaster Chicago
2 Bike 3 Schwinn Cincinatti
3 Motorcycle 1 Harley Omaha
3 Motorcycle 2 Kawasaki Atlanta
3 Motorcycle 3 Yamaha Dallas
*/