helping writing sql query
-
Hello, FOr example i have the following table class name 1 m 1 n 1 a 1 b 1 c 2 d 2 e 2 f I need the following result: class name 1 m,n,a,b,c 2 d,e,f I mean grouping by class and all the names in one row
-
Hello, FOr example i have the following table class name 1 m 1 n 1 a 1 b 1 c 2 d 2 e 2 f I need the following result: class name 1 m,n,a,b,c 2 d,e,f I mean grouping by class and all the names in one row
Use Cursor
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com
modified on Monday, September 20, 2010 9:16 AM
-
Hello, FOr example i have the following table class name 1 m 1 n 1 a 1 b 1 c 2 d 2 e 2 f I need the following result: class name 1 m,n,a,b,c 2 d,e,f I mean grouping by class and all the names in one row
michaelgr1 wrote:
all the names in one row
Do you mean "in one field"? If using Sql Server, you can write a custom aggregation function. There's an example somewhere on MSDN or the help. It may not be the best solution to this problem, but it's good to know how to do it.
-
michaelgr1 wrote:
all the names in one row
Do you mean "in one field"? If using Sql Server, you can write a custom aggregation function. There's an example somewhere on MSDN or the help. It may not be the best solution to this problem, but it's good to know how to do it.
I need it in a field. How can i do it? can you show me please? BTW, I have only read only access to the DB
-
Hello, FOr example i have the following table class name 1 m 1 n 1 a 1 b 1 c 2 d 2 e 2 f I need the following result: class name 1 m,n,a,b,c 2 d,e,f I mean grouping by class and all the names in one row
-
Hello, FOr example i have the following table class name 1 m 1 n 1 a 1 b 1 c 2 d 2 e 2 f I need the following result: class name 1 m,n,a,b,c 2 d,e,f I mean grouping by class and all the names in one row
You can use xml path
declare @tmp table (class int, name char)
insert into @tmp values (1, 'm')
insert into @tmp values (1, 'n')
insert into @tmp values (1, 'a')
insert into @tmp values (1, 'b')
insert into @tmp values (1, 'c')
insert into @tmp values (2, 'd')
insert into @tmp values (2, 'e')
insert into @tmp values (2, 'f')select class,
replace(replace((
select replace(name,' ','*') as 'data()'
from @tmp t2
where t1.class = t2.class
for xml path('')),' ',','),'*',' ') as name
from @tmp t1
group by class -
You can use xml path
declare @tmp table (class int, name char)
insert into @tmp values (1, 'm')
insert into @tmp values (1, 'n')
insert into @tmp values (1, 'a')
insert into @tmp values (1, 'b')
insert into @tmp values (1, 'c')
insert into @tmp values (2, 'd')
insert into @tmp values (2, 'e')
insert into @tmp values (2, 'f')select class,
replace(replace((
select replace(name,' ','*') as 'data()'
from @tmp t2
where t1.class = t2.class
for xml path('')),' ',','),'*',' ') as name
from @tmp t1
group by classI hate XML X| but it is an excellent answer!
Never underestimate the power of human stupidity RAH
-
You can use xml path
declare @tmp table (class int, name char)
insert into @tmp values (1, 'm')
insert into @tmp values (1, 'n')
insert into @tmp values (1, 'a')
insert into @tmp values (1, 'b')
insert into @tmp values (1, 'c')
insert into @tmp values (2, 'd')
insert into @tmp values (2, 'e')
insert into @tmp values (2, 'f')select class,
replace(replace((
select replace(name,' ','*') as 'data()'
from @tmp t2
where t1.class = t2.class
for xml path('')),' ',','),'*',' ') as name
from @tmp t1
group by classI find using STUFF to be slightly easier to read;
SELECT class,
STUFF(
(
SELECT
',' + name
FROM @tmp t2
WHERE t2.class = t1.class
FOR XML PATH('')
), 1, 1, '') AS data
FROM @tmp t1
GROUP BY class -
I find using STUFF to be slightly easier to read;
SELECT class,
STUFF(
(
SELECT
',' + name
FROM @tmp t2
WHERE t2.class = t1.class
FOR XML PATH('')
), 1, 1, '') AS data
FROM @tmp t1
GROUP BY classI'd like to STUFF all xml and it's derivatives where the sun don't shine.
Never underestimate the power of human stupidity RAH