Display Rows as Columns

[RJL1]

I have a query that returns 1 or more reords fot each item in out catalog along with the cross refference number

SELECT
DISTINCT(IC.X_REF_ITEM),
I.ITEM
FROM ITEM I WITH(NOLOCK)
  LEFT OUTER JOIN ITEM_CROSS_REFERENCE IC WITH(NOLOCK)
    ON IC.ITEM = I.ITEM
WHERE
I.ITEM = '10658'
AND I.COMPANY = '001'

results of the query

X_REF_ITEM                ITEM
---------------------------------------
000000200000010658        10658
051000005243              10658
10051000106589            10658

I would like to get the results as a single records like this

ITEM   X_REF_1             X_REF_2       X_REF_3  
------------------------------------------------------------
10658  000000200000010658  051000005243  051000106589

Any assitance is appreciated

RJL

 

[markros]

SQL Server 2005 and up

;with cte as (SELECT
DISTINCT(IC.X_REF_ITEM),I.ITEM, row_number() over (partition by I.Item order by IC.X_Ref_Item) as Row
FROM ITEM I WITH(NOLOCK)  
LEFT OUTER JOIN ITEM_CROSS_REFERENCE IC WITH(NOLOCK)    ON IC.ITEM = I.ITEM
WHEREI.ITEM = '10658'AND I.COMPANY = '001')

select Item, [1] as X_ref_1,
[2] as X_REF_2,
[3] as X_Ref_3
from cte PIVOT (max(X.Ref_Item) FOR Row in ([1],[2],[3])) pvt

PluralSight Learning Library