July 30, 2008 at 3:22 am
Hi,
I have an sql query that retrieves data for some ID where this ID has more than one data related to it.
Example:
ID Symbol
01 A
01 B
01 C
of course the primary key is (ID, Symbol).
when you select the ID and Symbol, it retieves the previous three rows.
I need to retrieve these data in one row by concatinating the Symbol to itself; i.e: I need the result to be just like the following:
ID Symbol
01 A and B and C
Is this possible???
July 30, 2008 at 4:28 am
Just check my blog
http://venkattechnicalblog.blogspot.com/2008/07/rows-to-columns-in-sql-server.html
Venkatesan Prabu .J
Thanks and Regards,
Venkatesan Prabu, 😛
My Blog:
http://venkattechnicalblog.blogspot.com/
July 30, 2008 at 11:52 am
Venkatesan's solution will work if you have fixed rows with fixed values. It will also require an extra step beyond what he wrote up to concatenate the list.
This will handle the whole thing:
--drop table #t
create table #T (
ID int not null,
Symbol char(1) not null,
constraint PK_T primary key (id, symbol),
String varchar(100))
insert into #t (id, symbol)
select 01,'A' union all
select 01,'B' union all
select 01,'C' union all
select 2,'D' union all
select 2,'E'
declare @Row varchar(100), @ID int
update #t
set @row = string =
case
when @id = id then coalesce(@row + ',' + symbol, symbol)
else symbol
end,
@id = id
from #t
;with CTE (ID, String, Row) as
(select id, string,
row_number() over (partition by id order by len(string) desc)
from #t)
select *
from cte
where row = 1
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply