October 22, 2008 at 8:19 am
Hi,
I have a table as follows.
RefOne RefTwo Item Purpose
100 200 Red Colour
100 200 Red Name
300 200 Cabbage Vegetable
Trying to find SQL to return
Red (Colour, Name), Cabbage (Vegetable)
In a single row.
I can get Red (Colour, Name, Vegetable)
or
Cabbage (Colour, Name, Vegetable)
by using COALESCE but cannot get the desired result.
Any help much appreciated. Thank you.
Colin
October 22, 2008 at 8:30 am
Could you give some details on how Colour and Name are associated with Cabbage?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 22, 2008 at 9:01 am
They are not connected, hence my wish to separate the se sections by a comma in my query result
The column ITEM represents companies which are carrying out various functions. These functions being represented by the Purpose column.
So, Red is doing 2 functioins say Architect abd builder, whilst cabbage is only performing one function. ALL functions are on the same project REFTWO.
Colin
October 22, 2008 at 9:19 am
Colin Betteley (10/22/2008)
Hi,I have a table as follows.
RefOne RefTwo Item Purpose
100 200 Red Colour
100 200 Red Name
300 200 Cabbage Vegetable
Trying to find SQL to return
Red (Colour, Name), Cabbage (Vegetable)
In a single row.
I can get Red (Colour, Name, Vegetable)
or
Cabbage (Colour, Name, Vegetable)
by using COALESCE but cannot get the desired result.
Any help much appreciated. Thank you.
Colin
Is there a one to one relationship between RefOne and Item?
😎
October 22, 2008 at 9:22 am
Sure is.
October 22, 2008 at 9:45 am
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 22, 2008 at 10:05 am
Based on the article Jack referenced, here is some sample code for you to start with:
create table #TempTable (
RefOne int,
RefTwo int,
Item varchar(10),
Purpose varchar(15)
);
insert into #TempTable
select 100,200,'Red','Colour' union all
select 100,200,'Red','Name' union all
select 300,200,'Cabbage','Vegetable';
select * from #TempTable;
with TempTable (
RefOne,
RefTwo,
Item,
Groups
) as (
SELECT
t1.RefOne,
t1.RefTwo,
t1.Item,
t1.Item + '(' +
STUFF((SELECT
',' + t2.Purpose
FROM
#TempTable t2
WHERE
t1.RefOne = t2.RefOne
FOR XML PATH('')),1,1,'') +
')'
FROM
#TempTable t1
GROUP BY
t1.RefOne,
t1.RefTwo,
t1.Item
)
select
t3.RefTwo,
STUFF((SELECT
',' + t4.Groups
FROM
TempTable t4
WHERE
t4.RefTwo = t3.RefTwo
FOR XML PATH('')),1,1,'')
from
TempTable t3
group by
t3.RefTwo;
drop table #TempTable;
Hope this gets yo where you are going!
😎
October 22, 2008 at 10:09 am
Sorry, it wont as written. You will have to modify the code as I wrote it for SQL Server 2005. You will have turn the CTE into a derived table (not too difficult), but I don't think the FOR XML will work in SQL Server 2000, so that may be a bit more difficult. Read the article jack referenced and it should help you rewrite my code.
😎
October 23, 2008 at 5:14 am
Thanks all. Your comments have pointed me in the right direction and I have now achieved the desired result. with all the other issues required by this report it has turned into quite a beast! 😉
Thanks again
Colin
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply