Join Query In SQL Server

Hi All,
I need you help about query in SQL Server. I have one table
ItemLocation. It has 2 fields
Item Location
A Loc1
B Loc2
C Loc1
D Loc3
E Loc2
F Loc2
I do query to select like this :
select Item from ItemLocation where Location='Loc1'
select Item from ItemLocation where Location='Loc2'
select Item from ItemLocation where Location='Loc3'
But i don't know how to join three query in above, so the result will
become like this
Loc1 Loc2 Loc3
A B D
C E
F
Location in ItemLocation is fix. Only three locations (Loc1, Loc2 and
Loc3).
Thanks for your help.Hi
Usually this is best left to your client application to display the data.
If you have a way of ranking the data then you can use something like:
CREATE TABLE #locs ( id int not null identity, type char(1), loc char(4) )
INSERT INTO #locs ( type, loc )
SELECT 'A', 'Loc1'
UNION ALL SELECT 'B', 'Loc2'
UNION ALL SELECT 'C', 'Loc1'
UNION ALL SELECT 'D', 'Loc3'
UNION ALL SELECT 'E', 'Loc2'
UNION ALL SELECT 'F', 'Loc2'
SELECT * from #locs
SELECT r.rank, d1.[type],d2.[type],d3.[type]
FROM
( SELECT 1 AS Rank
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10 ) AS r
LEFT JOIN
( SELECT ( SELECT COUNT(*) FROM #locs m where m.id <= l.id AND m.loc = l.loc
) as RANK,
[type],
loc
FROM #locs l WHERE l.loc = 'loc1') d1 ON r.rank = d1.rank
LEFT JOIN
( SELECT ( SELECT COUNT(*) FROM #locs m where m.id <= l.id AND m.loc = l.loc
) as RANK,
[type],
loc
FROM #locs l WHERE l.loc = 'loc2') d2 ON r.rank = d2.rank
LEFT JOIN
( SELECT ( SELECT COUNT(*) FROM #locs m where m.id <= l.id AND m.loc = l.loc
) as RANK,
[type],
loc
FROM #locs l WHERE l.loc = 'loc3') d3 ON r.rank = d3.rank
WHERE d1.rank IS NOT NULL
OR d2.rank IS NOT NULL
OR d3.rank IS NOT NULL
If you are going to have lots of rows then you may want to create a numbers
table.
For more information you may want to check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acda...
If you need to have a dynamic pivot you may also want to look at Itziks
articles on crosstabs in SQL Server magazine
http://www.sqlmag.com/Articles/ArticleID/15608/15608.html
If you are using SQL 2005 then you could use the PIVOT function.
John
"afang" wrote:
> Hi All,
> I need you help about query in SQL Server. I have one table
> ItemLocation. It has 2 fields
> Item Location
> A Loc1
> B Loc2
> C Loc1
> D Loc3
> E Loc2
> F Loc2
> I do query to select like this :
> select Item from ItemLocation where Location='Loc1'
> select Item from ItemLocation where Location='Loc2'
> select Item from ItemLocation where Location='Loc3'
> But i don't know how to join three query in above, so the result will
> become like this
> Loc1 Loc2 Loc3
> A B D
> C E
> F
> Location in ItemLocation is fix. Only three locations (Loc1, Loc2 and
> Loc3).
> Thanks for your help.
>