Two columns to pivot without fixed columns in TSQL?

Question

I have a data:

DECLARE @RawTable TABLE 
(
  ID INT NOT NULL
, CarDealer VARCHAR(100)
, id_CarDealer INT
, CityTo VARCHAR(100) NULL
, CityFrom VARCHAR(100) NULL
, Price NUMERIC(18,3) NULL
, CarCount INT
)


INSERT INTO @RawTable
(
ID,
CarDealer,
id_CarDealer,
CityTo,
CityFrom,
Price,
CarCount
)
VALUES
  (   1, 'TransLeader', 1, 'New York', 'Los Angeles', 5000, 10)
, (   2, 'AutoTrader',  2, 'New York', 'Los Angeles', 6000, 11)
, (   3, 'AO',          3, 'New York', 'Paris',       7000, 20)
, (   4,  'CarSale',    4, 'Paris',    'London',      4000, 50)

and I would like to get the following desired result:

Please, pay attention, that a list of CarDealer can be more than these - TransLeader, AutoTrader, AO, CarSale. It is just my example.

I understand that I need to use PIVOT function. But can't understand how to do it? Any help would be greatly appreciated. Thanks in advance.

EDIT

Clearly this is a duplicate question, but the dupe link did not cover some ot the requirements

Example

Declare @SQL varchar(max) = Stuff((Select Distinct ','+QuoteName(CarDealer+' Price') 
                                                  +','+QuoteName(CarDealer+' Count') 
                                    From RawTable 
                                    Order By 1 
                                    For XML Path('')
                                   ),1,1,'') 

Select  @SQL = '
Select CityTo
      ,CityFrom
      ,[TotalCarDealerPrice]
      ,[TotalCarDealerCount]
      ,'+@SQL+'
From (
        Select A.CityTo
              ,A.CityFrom
              ,B.*
         From  RawTable A
         Cross Apply ( values (''TotalCarDealerPrice'',Price)
                             ,(''TotalCarDealerCount'',CarCount)
                             ,(CarDealer+'' Price''   ,Price)
                             ,(CarDealer+'' Count''   ,CarCount)
                     ) B (Item,Value)
     ) A
 Pivot (sum([Value]) For [Item] in ([TotalCarDealerPrice],[TotalCarDealerCount],' + @SQL + ') ) p'
--Print @SQL
Exec(@SQL);

Returns

You have to use dynamic sql here. Either a dynamic cross tab or a dynamic pivot. — Sean Lange, Sep 17 '18 at 15:52
I agree with @SeanLange : The dynamic SQL should do the trick. You can use something like `SELECT DISTINCT CarDealer` and use the `FOR XML...` or something like this to paste the results into one string. Thin can then be used in your dynamic pivot statement. — Tyron78, Sep 17 '18 at 15:58
@JohnCappelletti, you are awesome! Thank you very much! That what I wanted cause link to duplicate question is wrong! Million thanks! — StepUp, Sep 17 '18 at 19:30
@JohnCappelletti could you be very kind to give to explain how this works `( Cross Apply ( values (''TotalCarDealerPrice'',AmountIncVATWeighted) ,(''TotalCarDealerCount'',FactLoadsCount) ,(OperatorName+'' Price'' ,AmountIncVATWeighted) ,(OperatorName+'' Count'' ,FactLoadsCount) ) B (Item,Value))` or a link to learn this code snippet. I know what `CROSS APPLY` is, but cannot understand `values` and other stuff. — StepUp, Sep 18 '18 at 07:29

Two columns to pivot without fixed columns in TSQL?

0 Answers0