SQLTeam.com | Weblogs | Forums

Converting rows to columns


#1

Hi,

I have the following data being returned in my query.
imageExternalReference NominalAccountRef NominalCostCentre AccountName
277828 4001 21 Sales - Pumps - C&U
277828 4010 21 Sales - Packaged Systems - C&U
277828 4012 21 Sales - Accessories EPC - C&U
277828 4080 21 Carriage - EPC - C&U
277828 4512 21 Accessories - C&U

I would like it to be displayed as follows:

imageExternalReference NominalAccountRef NominalCostCentre AccountName NominalAccountRef NominalCostCentre AccountName NominalAccountRef NominalCostCentre AccountName NominalAccountRef NominalCostCentre AccountName NominalAccountRef NominalCostCentre AccountName
277828 4001 21 Sales - Pumps - C&U 4010 21 Sales - Packaged Systems - C&U 4012 21 Sales - Accessories EPC - C&U 4080 21 Carriage - EPC - C&U 4512 21 Accessories - C&U

Can this be done ?

The query that produces the rows of data is like this:
select distinct ExternalReference, ol.NominalAccountRef, ol.NominalCostCentre, AccountName
, Dense_Rank() Over (Partition By EXternalReference order by o.DocumentDate desc) as Dedupe
from Elmbridge..SOPOrderReturn o Inner Join Elmbridge..SOPOrderReturnLine ol On o.SOPOrderReturnID = ol.SOPOrderReturnID
Left Outer Join Elmbridge..NLNominalAccount nl On nl.AccountNumber=ol.NominalAccountRef and nl.AccountCostCentre=ol.NominalCostCentre
where DocumentTypeID=3 and AccountName Is Not Null


#2

You would get responses if you post a create table script with inserts of sample data. Please post SQL using [code][\code] bb tags surrounding it. This will allow folks to work on a solution without having to guess. Thanks