troubleshooting Question

SQL - Group by ID

Avatar of Zack
ZackFlag for Australia asked on
Microsoft SQL ServerSQL
8 Comments1 Solution10 ViewsLast Modified:
Hi EE,

For the following code, how would I group by PRIRECID?


SELECT


    DAVS.RECID as PRIRECID,


    DAVSI.RECID as SECRECID,


    DA.NAME,


    DAVSI.DISPLAYVALUE,


dft.DESCRIPTION,bu.NAME As LocationDetails,dept.NAME As Dept, Cc.NAME as Costcentre
FROM


    dbo.DIMENSIONATTRIBUTEVALUESET                        AS DAVS


LEFT OUTER JOIN


    dbo.DIMENSIONATTRIBUTEVALUESETITEM    AS DAVSI    ON DAVS.RECID = DAVSI.DIMENSIONATTRIBUTEVALUESET


INNER JOIN


     dbo.DIMENSIONATTRIBUTEVALUE   AS  DAV            on DAV.RECID = DAVSI.DIMENSIONATTRIBUTEVALUE


INNER JOIN


     dbo.DIMENSIONATTRIBUTE  AS   DA                  on DA.RECID = DAV.DIMENSIONATTRIBUTE


LEFT JOIN DimensionAttributeDirCategory dadc
    ON dadc.DimensionAttribute = da.RecId


LEFT OUTER JOIN dbo.DIMENSIONFINANCIALTAG dft
ON davsi.DISPLAYVALUE = dft.VALUE
AND da.VIEWNAME = 'DimensionFinancialTag'
LEFT OUTER JOIN dbo.DIMATTRIBUTEOMDEPARTMENT dept
ON davsi.DISPLAYVALUE = dept.VALUE
AND da.VIEWNAME = 'DimAttributeOMDepartment'
LEFT OUTER JOIN dbo.DIMATTRIBUTEOMBUSINESSUNIT bu
ON davsi.DISPLAYVALUE = bu.VALUE
AND da.VIEWNAME = 'DimAttributeOMBusinessUnit'
LEFT OUTER JOIN dbo.DIMATTRIBUTEOMCOSTCENTER cc
ON davsi.DISPLAYVALUE = cc.VALUE
AND da.VIEWNAME = 'DimAttributeOMCostCenter'
Example resultset:

 
PRIRECIDSECRECIDNAMEDISPLAYVALUEDESCRIPTIONLocationDetailsDeptCostcentre
56371468265637146830E_DivisionCORNULLNULLCorporate ServicesNULL
56371468265637146826A_Location22229PNULLBalance SheetNULLNULL
56371468265637146827B_Business_UnitBALHCENULLNULLNULLBalance Sheet
56371468265637146828C_Product_ServiceZBALCEBalance SheetNULLNULLNULL
56371468265637146829D_PortfolioNDLSMining NULLNULLNULL

Any assistance is welcome.

Thank you. 

Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 8 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros