Convert Rows to Columns

[ambersaria420]

Hi All,

I need to help with converting rows to columns in SQL2k.

Input:
Id Name Role

58 Ron Doe Associate
58 Mark Bonas Doctor
59 Mike Johnson Doctor
59 John Smith Associate
102 Chris Carter Associate
102 Ron Doe Associate
102 James Jones Associate
Output should look like:

Id Doctor Assoc1 Assoc2 Assoc3

58 Mark Bonas Ron Doe NULL NULL
59 Mike Johnson John Smith NULL NULL
102 NULL Chris Carter Ron Doe James Jones
There could be more than 3 associates in the input but I only need 3
above columns for associates.

I used following query:
SELECT Q.sales_id,
doctor2= (SELECT Q2.name FROM view1 Q2 where Q2.role = 'doctor'
and Q2.sales_id = Q.sales_id),
assoc1= (SELECT Q2.name FROM view1 Q2 where Q2.role =
'associate' and Q2.sales_id = Q.sales_id),
assoc2= (SELECT Q2.name FROM view1 Q2 where Q2.role =
'associate' and Q2.sales_id = Q.sales_id),
assoc3= (SELECT Q2.name FROM view1 Q2 where Q2.role =
'associate' and Q2.sales_id = Q.sales_id)
FROM view1 Q
GROUP BY sales_id

and I get this error "Subquery returned more than 1 value" since there
are multiple associate for Id 102.

Thenks

[Simon Hayes]

<am***********@yahoo.com> wrote in message
news:11**********************@c13g2000cwb.googlegr oups.com...

Hi All,

I need to help with converting rows to columns in SQL2k.

Input:
Id Name Role

58 Ron Doe Associate
58 Mark Bonas Doctor
59 Mike Johnson Doctor
59 John Smith Associate
102 Chris Carter Associate
102 Ron Doe Associate
102 James Jones Associate
Output should look like:

Id Doctor Assoc1 Assoc2 Assoc3

58 Mark Bonas Ron Doe NULL NULL
59 Mike Johnson John Smith NULL NULL
102 NULL Chris Carter Ron Doe James Jones
There could be more than 3 associates in the input but I only need 3
above columns for associates.

I used following query:
SELECT Q.sales_id,
doctor2= (SELECT Q2.name FROM view1 Q2 where Q2.role = 'doctor'
and Q2.sales_id = Q.sales_id),
assoc1= (SELECT Q2.name FROM view1 Q2 where Q2.role =
'associate' and Q2.sales_id = Q.sales_id),
assoc2= (SELECT Q2.name FROM view1 Q2 where Q2.role =
'associate' and Q2.sales_id = Q.sales_id),
assoc3= (SELECT Q2.name FROM view1 Q2 where Q2.role =
'associate' and Q2.sales_id = Q.sales_id)
FROM view1 Q
GROUP BY sales_id

and I get this error "Subquery returned more than 1 value" since there
are multiple associate for Id 102.

Thenks

This is very awkward to write in TSQL, especially since the number of
associates may vary - you would need a cursor (maybe even nested cursors) to
loop through the table for each ID. A better solution is to do this in the
front end or using a reporting tool.

Simon

[TSA]

I changed the query to following:

select distinct v1.sales_id, v1.name as doctor2 , v2.name as assoc1,
v3.name as assoc2, v4.name as assoc3

from

(select sales_id, max(case when role = 'Doctor' then name else NULL
end) as name from view1

group by sales_id) v1

left join (select sales_id , name from view1 where role = 'Associate'
) v2 on v1.sales_id = v2.sales_id

left join (select sales_id , name from view1 where role = 'Associate'
) v3 on v1.sales_id = v3.sales_id and (v3.name is null or v3.name >
v2.name)

left join (select sales_id , name from view1 where role = 'Associate'
) v4 on v1.sales_id = v4.sales_id and (v4.name is null or( v4.name >
v2.name and v4.name > v3.name))
However now it return extra row if there is more than one associate.

New Output:

58 Mark Bonas Ron Doe NULL NULL
59 Mike Johnson john2 smith NULL NULL
102 NULL Chris Carter James Jones Ron Doe
102 NULL Chris Carter Ron Doe NULL
102 NULL James Jones Ron Doe NULL
102 NULL Ron Doe NULL
NULL
Thanks Again.

[louis]

It's ugly to write this in SQL. If you really want to do this, you can
do it using temp tables.
--------------------------------------------------------------------------------------------------------------------------

create table #T
(i int, name varchar(50), role varchar(50))

insert #T
values('58','Ron Doe','Associate')
insert #T
values('58','Mark Bonas','Doctor')
insert #T
values('59','Mike Johnson','Doctor')
insert #T
values('59','John Smith','Associate')
insert #T
values('102','Chris Carter','Associate')
insert #T
values('102','Ron Doe','Associate')
insert #T
values('102','James Jones','Associate')

-- doctor and first associate
select
i,
min(case when role='doctor' then name else 'zzz' end) as doctor,
min(case when role='associate' then name else 'zzz' end) as
associate
into #T1
from #T
group by i

-- second associate
select
#T.i,
min(case when role='associate' then name else 'zzz' end) as
associate
into #T2
from #T
left join #T1 on #T.i=#T1.i and #T.name=#T1.associate
where #T1.associate is null
group by #T.i

-- third associate
select
#T.i,
min(case when role='associate' then name else 'zzz' end) as
associate
into #T3
from #T
left join #T1 on #T.i=#T1.i and #T.name=#T1.associate
left join #T2 on #T.i=#T2.i and #T.name=#T2.associate
where #T1.associate is null and #T2.associate is null
group by #T.i

-- output
select #T1.i, #T1.doctor, #T1.associate as assoc1, #T2.associate as
assoc2, #T3.associate as assoc3
from #T1
join #T2 on #T1.i=#T2.i
join #T3 on #T1.i=#T3.i