Avatar of Zack
Zack
Flag for Australia

asked on 

SQL - Group by ID

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. 

SQLMicrosoft SQL Server

Avatar of undefined
Last Comment
Zack

8/22/2022 - Mon