dynamic pivot

gistfile1.txt

DECLARE @cols  AS NVARCHAR(MAX)=''
DECLARE @query AS NVARCHAR(MAX)=''

SELECT @cols = @cols + QUOTENAME(Crop) + ',' FROM (select distinct [Crop] from [FAF40].[dbo].[county_farm_acres2014] ) as tmp

set @query = 
'SELECT * from 
(
    select StateCode, CountyCode, Crop from [FAF40].[dbo].[county_farm_acres2014]
) src
pivot 
(
    max(PlantedAcres) for Crop in (' + @cols + ')
) piv'

print(@cols)
print(@query)
execute(@query)
go

SELECT * from 
(
    select StateCode, CountyCode, Crop, sum(PlantedAcres) as PlantedAcres from [FAF40].[dbo].[county_farm_acres2014]
	group by StateCode, CountyCode, Crop
) src
pivot 
(
    Sum(PlantedAcres) for Crop in ([WAX JAMBOO FRUIT],[TURNIPS],[WALNUTS],[CURRANTS],[TURN AREAS],[GOURDS],[BUCKWHEAT],[QUINCES],[LENTILS],[ALGAE],[RHUBARB],[HYBRID POPLAR TREES],[SORGHUM-  DUAL PURPOSE],[BROCCOLI],[SESAME],[COFFEE],[SQUASH],[WATER CRESS],[PAPAYA],[CAULIFLOWER],[JACK FRUIT],[FORAGE SOYBEAN/SORGHUM],[TOBACCO FIRE CURED],[PERSIMMONS],[TOBACCO BURLEY],[POMEGRANATES],[ALFALFA],[LONGAN],[BEETS],[FLAX],[EMMER],[CAIMITO],[KOCHIA (PROSTRATA)],[RADISHES],[CRANBERRIES],[SOYBEANS],[SUGARCANE],[RUTABAGA],[PEPPERS],[SAPODILLA],[CONSERVATION STEWARDSHIP PROGRAM],[MUSHROOMS],[GRASS],[TANGOS],[TANGELOS],[COTTON- ELS],[PUMMELO],[MAYHAW BERRIES],[INDUSTRIAL RICE],[RAMBUTAN],[SORGHUM FORAGE],[FIGS],[COTTON- Upland],[CAMELINA],[KOHLRABI],[TOBACCO FLUE CURED],[PISTACHIOS],[GINGER],[CHRISTMAS TREES],[ACEROLA],[PERENNIAL PEANUTS],[SUGAR BEETS],[MACADAMIA NUTS],[TOMATOES],[PEJIBAYE (HEART OF PALM)],[RAISINS],[ASPARAGUS],[GREENS],[HORSERADISH],[FINFISH],[SHRUBS/FORBS],[LIMES],[WATER IMPOUNDMENT STRUCTURE],[TOBACCO CIGAR BINDER],[ONIONS],[MOHAIR],[SORGHUM],[EGGPLANT],[GAILON],[PASSION FRUITS],[PARSNIP],[CARROTS],[QUINOA],[CHERIMOYA],[CORN],[WOODLAND/NATIVE UNDERSTORY],[PINEAPPLE],[GRAPES],[SAPOTE],[CRP],[HAZEL NUTS],[TOBACCO CIGAR FILLER BINDER],[MIXED FORAGE],[VETCH],[CHINESE BITTER MELON],[MANGOSTEEN],[AMARANTH GRAIN],[MILLET],[COCONUTS],[RICE- Wild],[380],[MAPLE SAP],[AVOCADOS],[JERUSALEM ARTICHOKES],[ARONIA (CHOKEBERRY)],[RICE- Sweet],[BARLEY],[BIRDSFOOT/TREFOIL],[MEADOWFOAM],[YAM],[TOBACCO BURLEY 31V],[GRASSLAND RESERVE PROGRAM],[OKRA],[TEA],[LESPEDEZA],[PEARS],[CRUSTACEAN],[BLUEBERRIES],[PEAS],[WETLAND BANK RESERVE],[CHIA],[CHUFES],[GOOSEBERRIES],[SUNN HEMP],[GENIP],[HERBS],[TOMATILLOS],[POTATOES SWEET],[GUAR],[SKIP ROWS],[CITRON MELON],[TEFF],[KAMUT],[APRICOTS],[LETTUCE],[GUAVA],[BEANS],[EINKORN],[KIWIFRUIT],[TOBACCO-  CIGAR WRAPPER],[PLUMCOTS],[ATEMOYA],[CANARY SEED],[WAMPEE],[OATS],[ORANGES],[POTATOES],[COMMERCIAL GARDEN ],[SAFFLOWER],[CANEBERRIES],[427],[TRUFFLES],[CASHEW],[HESPERALOE],[STRAWBERRIES],[ALOE VERA],[PUMPKINS],[KUMQUATS],[SHALLOTS],[DATES],[GARLIC],[TOBACCO CIGAR FILLER],[TOBACCO VIRGINIA FIRE CURED],[TRITICALE],[CABBAGE],[HOPS],[CUCUMBERS],[381],[BAMBOO SHOOTS],[MOLLUSK],[TANGERINES],[ELDERBERRIES],[MANGOS],[JOJOBA],[HONEY],[CANTALOUPES],[TOBACCO DARK AIR CURED],[LEMONS],[INDIGO],[WILDLIFE HABITAT INCENTIVE PROGRAM],[CHICORY/RADICCHIO],[LYCHEE],[PERIQUE TOBACCO],[CANOLA],[APPLES],[GUAYULE],[EQIP],[WATERBANK],[PECANS],[PLUMS],[KOREAN GOLDEN MELON],[BREADFRUIT],[FALLOW],[LEEKS],[CASSAVA],[PENNYCRESS],[SCALLIONS],[PEANUTS],[ARTICHOKES],[CASABA MELON],[RICE],[NECTARINES],[HOME GARDEN],[RAPESEED],[CELERIAC],[JUJUBE],[ISRAEL MELONS],[TOBACCO MARYLAND],[311],[JUNEBERRIES],[MILKWEED],[PEACHES],[MUSTARD],[TARO],[GINSENG],[PRUNES],[WHEAT],[WATERMELON],[EMERGENCY WATERSHED/FLOODPLAIN],[KENAF],[BROCCOFLOWER],[CRAMBE],[CELERY],[GRAPEFRUIT],[CARAMBOLA (STAR FRUIT)],[BROCCOLO-CAVALO],[TREES- Timber],[CHERRIES],[MULBERRIES],[OLIVES],[TANNIER],[ALMONDS],[DASHEEN],[PLANTAIN],[RYE],[SUNFLOWERS],[HUCKLEBERRIES],[CANARY MELON],[HONEYDEW],[CALALOO],[CRENSHAW MELON],[FLOWERS],[SPRITE MELON],[WILDLIFE FOOD PLOT],[BRUSSEL SPROUTS],[GUAMABANA/SOURSOP],[CLOVER],[NURSERY],[CHESTNUTS],[WETLAND RESERVE PROGRAM],[AMBROSIA],[SPELTZ],[421],[BANANAS])
) piv
go