What T-SQL statement am I looking for?

6 Apr 2006 5:27 PM

mahalie

Hi, Just hoping for a hint here...I've got a query that returns names
and project roles, like this:

MarkW    Project Designer
MarkW    Project Manager
MarkW    Project Architect
MarkW    Architect
MarkW    Interior Designer
MasaruH    Project Director
MasaruH    Project Designer
MatthewS    Project Manager
MatthewS    Planner

I'm trying to find the clause or syntax or join type or whatever to
create this:
MarkW
Project Designer
Project Manager
Project Architect
Architect
Interior Designer
MasaruH
Project Director
Project Designer
MatthewS
Project Manager
Planner

Etc.

I know how to do this manipulating a recordset using VB on a webpage -

> but I really just want to do this in TSQL so I can export it to a CSV file.

Any hints? I'm looking for a general pointer and I'm happy to go read
BOL on it. Just not sure where to start.

6 Apr 2006 5:36 PM

Alexander Kuznetsov

create table #table1 (id int, info char(10))

insert into #table1 values (1, 'data1')
insert into #table1 values (2, 'data2')
insert into #table1 values (3, 'data3')
insert into #table1 values (4, 'data4')
insert into #table1 values (5, 'data5')

create table #table2 (table1_id int, rec_id int, description char(10))

insert into #table2 values (1, 1, '1_test_1')
insert into #table2 values (1, 2, '1_test_2')
insert into #table2 values (2, 1, '2_test_1')
insert into #table2 values (3, 1, '3_test_1')
insert into #table2 values (4, 1, '4_test_1')
insert into #table2 values (4, 2, '4_test_2')
insert into #table2 values (4, 3, '4_test_3')
insert into #table2 values (4, 3, '4_test_4')
go
select info, description from(
select id, '' info, 1 t, description from #table1 join #table2 on
#table1.id=#table2.table1_id
union all
select id, info, 0 t, '' description from #table1
) t
order by id, t

info       description
---------- -----------
data1
           1_test_1
           1_test_2
data2
           2_test_1
data3
           3_test_1
data4
           4_test_1
           4_test_2
           4_test_3
           4_test_4
data5

(13 row(s) affected)

6 Apr 2006 5:40 PM

mahalie

Sweet, so...sorry, I'm a noob (obviously) but is there a term for this
sort of thing?

6 Apr 2006 5:50 PM

mahalie

mahalie wrote:

> Sweet, so...sorry, I'm a noob (obviously) but is there a term for this
> sort of thing?

Doh. Nevermind. I need to read up on UNION. Thanks again!