Post reply

pivot the coloum when occurences are not known

  • August 6, 2010 at 2:47 am

    #222276

    This is my MS SQL query

    select

    x1.ItemName,

    x4.ContractorCommodityCode,

    x4.ShortMaterialDescription,

    x2.ItemName as SpoolName,

    Sum (x7.DryWeight) as TotalWeight,

    sum (x8.Length) as TotalLength,

    count (*) as Qty,

    TotalQty = coalesce(sum (x8.Length), count (*))

    from JpartOcc x6

    join JnamedItem x1 on x1.oid=x6.oid

    join XSpoolHierarchy x3 on x3.oiddestination = x1.oid

    join JNamedItem x2 on x2.oid=x3.oidOrigin

    Join XPartOccToMaterialControlData x5 on x5.oidOrigin=x1.oid

    join JGenericMaterialControlData x4 on x4.oid=x5.oidDestination

    join JWeightCG x7 on x7.oid = x6.oid

    left Join JRteStockPartOccur x8 on x8.oid =x6.oid

    group by x1.ItemName,x4.ContractorCommodityCode,x4.ShortMaterialDescription,x2.ItemName

    order by x2.ItemName

    I would like to pivot the coloum SpoolName. And number of occurences are not known.

  • August 6, 2010 at 9:22 am

    #1203601

    Check out the articles in my signature on Cross-Tabs / Pivot tables, Part 1 and 2. Part 2 delves into dynamic solutions, which sounds like what you would need.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply