declare @table table
(
ID int,
PrdtType varchar(10),
PrdtCode varchar(10),
CatNo int
)


INSERT INTO @Table values(101, 'dues' ,'Mem', 100)
INSERT INTO @Table values(101, 'Vol', 'charity', 200) 
INSERT INTO @Table values(101, 'chapt', 'CHi', 300 )

INSERT INTO @Table values(102, 'dues' ,'jke', 100)
INSERT INTO @Table values(102, 'Vol', 'wcw', 200) 
INSERT INTO @Table values(102, 'chapt', 'ewce', 300 )

select t1.ID,left(t2.list,len(t2.list)-1) as Atributes
FROM (select distinct ID from @table) t1
cross apply (select CAST(CatNo as varchar(4)) + ' ' + PrdtCode + ',' as [text()]
             from @table
             where ID = t1.ID
             for xml path(''))t2(list)
output
---------------------
ID          Atributes
----------- ------------------------------
101         100 Mem,200 charity,300 CHi
102         100 jke,200 wcw,300 ewce

quote:

---

Originally posted by miky001@yahoo.com

Hi
I need help with a query for the following:

I have 3 tables:

TABLE 1
name No_of_credits
BOB 16
TOM 14
KEVIN 18

TABLE 2
name No_of_vehicles
BOB 0
JAMES 2

TABLE 3
name No_of_cell_phones
PETER 2
BOB 0
KEITH 1
PATRICK 1
KEVIN 1
JAMES 1




HOW DO I GET RESULTS LIKE THIS:

NAME CREDITS VEHICLES PHONES
BOB 16 0 0
TOM 14 NO DATA NO DATA
KEVIN 18 NO DATA 1
JAMES NO DATA 2 1
PETER NO DATA NO DATA 2
KEITH NO DATA NO DATA 1
PATRICK NO DATA NO DATA 1


Thanks in advance!

Miky

Miky001

---

SELECT COALESCE(t1.name,t2.name,t3.name) AS NAME,
       COALESCE(CAST(No_of_credits AS varchar(4)),'NO DATA') AS CREDITS,
       COALESCE(CAST(No_of_vehicles AS varchar(4)),'NO DATA') AS VEHICLES,
       COALESCE(CAST(No_of_cell_phones AS varchar(4)),'NO DATA') AS PHONES
FROM TABLE1 t1
FULL OUTER JOIN TABLE2 t2
ON t2.name=t1.name
FULL OUTER JOIN TABLE3 t3
ON t3.name=t1.name