MS SQL Server 2008, making vertical data horizontal

I need to create a view based on this query:
select                  s.ord_No [shop_ord],
                            h.ord_no [oe_ord]
from                    shopfile s
left outer join      cusordfile h
        on                ltrim(s.ord_no) = rtrim(h.oe_po_no)
where                   h.ord_no is not null
which returns these results:
shop_ord      oe_ord
    3328        89907
    3328        90437
      32         131123

Note that the relationship of shop_ord to oe_ord is one to many and that "many" is undefined (currently believed to be a max of 4)

We would like to see the results like this:
shop_ord     oe_ord     oe_ord     oe_ord     oe_ord
    3328        89907      90437
      32          131123


Can that be done?
Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
there will be several ways to do this, check out for "crosstab" and "pivot" data.

Trying to go the PIVOT way to achieve this...

Close in this example but not perfect...Can we get input Guy?

DROP TABLE #shopfile
DROP TABLE #cusordfile
--first temp table
CREATE TABLE #shopfile (ord_No int)
INSERT INTO #shopfile (ord_No)
VALUES (3328),(32)
--second temp table
CREATE TABLE #cusordfile (oe_po_no int,ord_no int)
INSERT INTO #cusordfile (oe_po_no, ord_no)
VALUES (3328 ,89907),(3328, 90437),(32,131123)

--put the data into another temp table
select   s.ord_No [shop_ord], 
         h.ord_no [oe_ord] 
INTO #PivotTable
from				#shopfile s 
left outer join     #cusordfile h 
on       ltrim(s.ord_no) = rtrim(h.oe_po_no) 
where    h.ord_no is not null 

--PIVOT that bad boy up
FROM #PivotTable

SELECT shop_ord, [89907] AS OE1 ,[90437] AS OE2, [131123] AS OE3
FROM #PivotTable as p

 MAX(oe_ord) FOR [oe_ord] 
  IN ([89907],[90437],[131123] )
  ) AS pvt 

Open in new window

g_johnsonAuthor Commented:
Ultimtely, this is what we did:

CReate view vw_OrderString

with cte as
(select distinct S.Ord_No FROM ShopFile S)       
select cte.ord_no as SF_Ord_No,  
      Quote_List= stuff((select ',  ' + Rtrim(Ltrim(ord_no)) FROM cusordfile
      where Rtrim(Ltrim(oe_po_no))= Rtrim(Ltrim(Cte.Ord_No))      
      order by ord_no asc
for XML path(''),TYPE).value('.','varchar(max)'),1,1,'') from CTE
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.