troubleshooting Question

SQL - Group by ID

Avatar of Zack
ZackFlag for Australia asked on
SQLMicrosoft SQL Server
8 Comments2 Solutions12 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'

Open in new window

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. 

ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 8 Comments.
Start Free Trial
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 2 Answers 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