CODEWITHSUNDEEP
sqlsql-server
Koen
Koen

Reputation: 868

SQL pivot / VLOOKUP query

I have the following simplified table so each stock code has multiple warehouses and I want to "pivot" it so I can get the cost for each warehouse just in 1 line for the stockcode

Stockcode | Warehouse | Cost 
A100        WH          100$
A100        ZZ          200$

What I would like to have is the following. So only 1 stockcode line with the warehouse costs pivoted.

Stockcode | Cost WH | Cost ZZ
A100        100$      200$

I currently did it by exporting it to excell, creating a new sheet and add a new column to the sheet using a VLOOKUP to the main table and add the ZZ cost like that.

How would you turn this around in SQL? I am a bit stuck with understanding how to transform this tabular data in such a way.

Upvotes: 1

Views: 222

Answers (3)

Cristian Lupascu
Cristian Lupascu

Reputation: 40576

Here's how to do this query using PIVOT:

select Stockcode, [WH] as CostWH, [ZZ] as CostZZ
from (
  select Stockcode, Warehouse, Cost
  from MyTable
) p
pivot (
  MAX(Cost)
  for Warehouse in ([WH], [ZZ])
) as pvt
order by Stockcode;

Test it on SQLFiddle

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

However, you could also do that by using simple case expression if you don't want to go pivot or dynamic pivot

SELECT 
          Stockcode , 
          MAX( CASE (Warehouse) WHEN 'WH' THEN Cost END)[Cost WH],
          MAX(CASE (Warehouse) WHEN 'ZZ' THEN Cost END) [Cost ZZ]
FROM <table> GROUP BY Stockcode

Dynamic pivot way :

DECLARE @Col nvarchar(max), @query nvarchar(max)

select @Col = STUFF(
                    (SELECT  ',' +QUOTENAME(Warehouse) FROM <table> FOR XML PATH('')), 
                    1, 1, ''

                )

SELECT @query = N'select Stockcode, [WH] as CostWH, [ZZ] as CostZZ
             from (
                select Stockcode, Warehouse, Cost
                 from <table>) p
                    pivot (max(Cost) for Warehouse in ('+@Col+')
                    ) as pvt order by Stockcode;'

                    exec(@query)

Result :

Stockcode  Cost WH  Cost ZZ
A100       100$    200$

Upvotes: 1

Sergey Menshov
Sergey Menshov

Reputation: 3906

I think (Stockcode,Warehouse) is unique in your table. And I think you can use PIVOT like it

SELECT Stockcode,[WH],[ZZ]
FROM TestData PIVOT(MAX(Cost) FOR Warehouse IN([WH],[ZZ])) p

And use dynamic query for generate query for all the warehouses

DECLARE @Warehouses varchar(500)=''

SELECT @Warehouses+=CONCAT(',[',Warehouse,']')
FROM
  (
    SELECT DISTINCT Warehouse
    FROM TestData
  ) q
ORDER BY Warehouse

SET @Warehouses=STUFF(@Warehouses,1,1,'')

PRINT @Warehouses

DECLARE @query varchar(2000)=CONCAT('SELECT Stockcode,',@Warehouses,'
FROM TestData PIVOT(MAX(Cost) FOR Warehouse IN(',@Warehouses,')) p')

PRINT @query

EXEC(@query)

My another answer similar to it - Display count results of requests with results of jobs horizontally and locations vertically 3 tables

Upvotes: 1

Related Questions