Create sql pivot on more row&column

I have to combine in PIVOT multiple columns / rows. let me explain with an example:

My start table:

PriceListId | ItemId | Stock | PriceIT | PriceES | PriceAT | OriginPriceListId

28 | 25760 | 51 | 46.76 | 49.20 | 44.49 | 301874
36 | 25760 | 51 | 56.76 | 59.20 | 54.49 | 301874
28 | 25761 | 4 | 45.66 | 48.26 | 43.57 | 301875
36 | 25761 | 4 | 55.66 | 58.26 | 53.57 | 301875
28 | 25763 | 100 | 47.97 | 50.57 | 45.70 | 301877
36 | 25763 | 100 | 57.97 | 60.57 | 55.70 | 301877

How I wish:

ItemId | Stock | OriginPriceListId| PriceIT_28 | PriceES_28 | PriceAT_28 | PriceIT_36 | PriceES_36 | PriceAT_36 

25760 | 51 | 301874| 46.76 | 49.20 | 44.49 | 56.76 | 59.20 | 54.49 
25761 | 4 | 301875| 45.66 | 48.26 | 43.57 | 55.66 | 58.26 | 53.57
25763 | 100 | 301877| 47.97 | 50.57 | 45.70 | 57.97 | 60.57 | 55.70 

My code only for PriceIT :

with pli as (

select PriceListId, ItemId, Stock, PriceIT, OriginPriceListId from dbo.fn_PL_PriceListItems(28) 

union all

select PriceListId, ItemId, Stock, PriceIT, OriginPriceListId from dbo.fn_PL_PriceListItems(36)

) 

select top 10  ItemId, Stock, OriginPriceListId,[28],[36]
from(

select PriceListId, ItemId, Stock, PriceIT,OriginPriceListId from pli

) a
PIVOT (min(PriceIT) FOR PriceListId in ([28],[36])) as pvt

Result:

ItemId |Stock   | OriginPriceListId | 28 | 36

124315 | 8 | 860680 | 120.79 | 120.7

124314 | 8 | 860679 | 301.75 | 301.75

124313 | 33 | 860678 | 44.51 | 44.51

Answer

I am not sure how you are getting the data from the function call. Based on your requirement, I have created a table and inserted the datas which you have provided.

CREATE TABLE TESTPRICE
(PriceListId INT, ItemId INT, Stock INT, PriceIT DECIMAL(10,2),
 PriceES DECIMAL(10,2), PriceAT DECIMAL(10,2), OriginPriceListId INT)


INSERT INTO TESTPRICE VALUES
(28, 25760 , 51 , 46.76 , 49.20 , 44.49 , 301874),
(36, 25760 , 51 , 56.76 , 59.20 , 54.49 , 301874),
(28, 25761 , 4 , 45.66 , 48.26 , 43.57 , 301875),
(36, 25761 , 4 , 55.66 , 58.26 , 53.57 , 301875),
(28, 25763 , 100 , 47.97 , 50.57 , 45.70 , 301877),
(36, 25763 , 100 , 57.97 , 60.57 , 55.70 , 301877)

Based on your requirement I have pivoted the records each for IT, ES and AT.

with cte1 as(
SELECT pvt.ItemId, pvt.Stock, pvt.OriginPriceListId, pvt.[28] AS 'PriceIT_28', pvt.[36] as 'PriceIT_36' 
FROM(SELECT  ItemId, Stock, OriginPriceListId,PriceIT,PriceListId FROM TestPrice) AS t PIVOT (min (PriceIT) FOR PriceListId IN (  [28],[36])) AS pvt),

cte2 as(
SELECT pvt.ItemId, pvt.Stock, pvt.OriginPriceListId, pvt.[28] AS 'PriceAT_28', pvt.[36] as 'PriceAT_36' 
FROM(SELECT  ItemId, Stock, OriginPriceListId,PriceAT,PriceListId FROM TestPrice) AS t PIVOT (min (PriceAT) FOR PriceListId IN (  [28],[36])) AS pvt),

cte3 as(
SELECT pvt.ItemId, pvt.Stock, pvt.OriginPriceListId, pvt.[28] AS 'PriceES_28' , pvt.[36] 'PriceES_36' 
FROM(SELECT  ItemId, Stock, OriginPriceListId,PriceES,PriceListId FROM TestPrice) AS t PIVOT (min (PriceES) FOR PriceListId IN (  [28],[36])) AS pvt)

SELECT DISTINCT a.ItemId, a.Stock, a.OriginPriceListId, PriceIT_28, PriceES_28, PriceAT_28, PriceIT_36, PriceES_36, PriceAT_36
FROM cte1 a join cte2 b  ON a.ItemId=b.ItemId 
JOIN cte3 c ON b.ItemId=c.ItemId

Using the above query I am getting the desired output. Let me know if you have any issues with the understanding.

ItemId  Stock   OriginPriceListId   PriceIT_28  PriceES_28  PriceAT_28  PriceIT_36  PriceES_36  PriceAT_36
25760   51      301874              46.76        49.20       44.49       56.76       59.20      54.49
25761   4       301875              45.66        48.26       43.57       55.66       58.26      53.57
25763   100     301877              47.97        50.57       45.70       57.97       60.57      55.70