Pivot table multiple column values in a single column

Question

I've got a table like that,

id  PID       VID    Type        PriceA  PriceB
41  297       2      128         70.000  80.000 
42  297       3      256         90.000  100.000    
43  297       4      300        110.000  120.000    
44  297       5      400        130.000  140.000    
45  294       2      128         10.000  50.000 
46  294       3      256         20.000  60.000 
47  294       4      300         30.000  70.000 
48  294       5      400         40.000  80.000
49  294       6      450         50.000  85.000
50  294       7      470         45.000  75.000

What I want to do is a query with PID parameter and get a result like that

PID  |        128       |         256       |        300         |        400      
297  | 70.000 / 80.0000 |  90.000 / 100.000 | 110.000 / 120.000  | 130.000 / 140.000

I have tried several different options pivot table subquery etc., but I could not make it.

score 1 · Accepted Answer · edited May 23 '17 at 12:28

This is full working exmaple:

CREATE TABLE DataSource
(
    [ID] TINYINT
   ,[PID] SMALLINT
   ,[VID] TINYINT
   ,[Type] SMALLINT
   ,[PriceA] VARCHAR(32)
   ,[PriceB] VARCHAR(32)
)

INSERT INTO DataSource ([ID],[PID],[VID],[Type],[PriceA],[PriceB])
VALUES (41,297,2,128,70.000,80.000)
      ,(42,297,3,256,90.000,100.000)    
      ,(43,297,4,300,110.000,120.000)    
      ,(44,297,5,400,130.000,140.000)    
      ,(45,294,2,128,10.000,50.000) 
      ,(46,294,3,256,20.000,60.000) 
      ,(47,294,4,300,30.000,70.000) 
      ,(48,294,5,400,40.000,80.000)
      ,(49,294,6,450,50.000,85.000)
      ,(50,294,7,470,45.000,75.000)

SELECT *
FROM 
(
   SELECT [PID]
         ,[Type]
         ,[PriceA] + ' / ' + [PriceB] AS [Price]
   FROM DataSource
) AS DataSource
PIVOT
(
  MAX([Price]) FOR [Type] IN ([128],[256],[300],[400],[450], [470])
) PVT

The output is like this:

enter image description here

The idea is to build the column [PriceA] + ' / ' + [PriceB] and then to make the pivot.

Note, that I have hardcoded the posible [Type] values. If you need to make this dynamic you can build a dynamic PIVOT building the SQL string and then executing it with sp_executesql procedure like is done here.

Pivot table multiple column values in a single column

1 Answers1