1
select 
     distinct state,sector,
             pt.RSGHDPE as [PPC],pt.OPC43HDPE as [OPC43],
             pt.OPC53HDPE as [OPC53],pt.RSFHDPE as [RSF]
from RMCL_DESPSUM_This_year as t
pivot (min(t.factory) for t.item_name in( RSGHDPE,OPC53HDPE,OPC43HDPE,RSFHDPE)) as pt
where inv_Date between'2017-04-01 00:00:00.000'and getdate()
order by state

I have given one value from item code which is RSGHDPE , but i have 6 items which starts with RSG likewise for other itemnames also

what i am looking for is below one or any other method 

pivot (min(t.factory) for t.item_name like( 'RSG%','OPC53%','OPC43%','RSF%')) as pt
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
Flash
  • 13
  • 5
  • Possible duplicate-https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query – DatabaseCoder Feb 26 '18 at 09:26
  • @BhatiaAshish i did'nt find any column name to search in that link and i have some 250 values in column i need only some values to be rows – Flash Feb 26 '18 at 09:36
  • Find your columns list first and use it in a dynamic sql – SqlKindaGuy Feb 26 '18 at 09:44
  • i am very new to sql server can u explain me clearly @plaidDK – Flash Feb 26 '18 at 09:51
  • You need to declare a variable which holds the list of the columns you want in your pivot. This will be a much better solution since you can isolate which columns you actually want to target. Look for dynamic pivot examples. There is a lot. Just like @BhatiaAshish writes – SqlKindaGuy Feb 26 '18 at 09:55

1 Answers1

1

Since you are looking for few item_names then you could use some conditional aggregation approach. 

SELECT  
        state, sector,
        MIN(CASE WHEN item_name LIKE 'RSG%' THEN factory END) PPC,
        ...
        MIN(CASE WHEN item_name LIKE 'RSF%' THEN factory END) RSF
FROM RMCL_DESPSUM_This_year as t
WHERE inv_Date between @start AND @end
GROUP BY state,sector
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52