June 10, 2009 at 11:47 pm
Hi...
value id
----- -----
XXXX 1
yyyyy 1
vvvvv 2
I had data like this I need to
value id
----- - --
xxxx,YYYYY 1
VVVVV 2
Than'q
June 11, 2009 at 12:44 am
Hi,
try this
/*
create table #temp
(
Value varchar(10),
ID1 int
)
insert into #temp
select 'XXXX',1
union all
select 'YYYY',2
union all
select 'ZZZZ',3
union all
select 'AAAA',3
select * from #temp
*/
select id1,max(value) from #temp
group by id1
having count(*) = 1
union all
select id1,(max(Value)+','+min(Value))value from #temp
group by id1
having count(*) >1
ARUN SAS
June 11, 2009 at 4:55 pm
There's probably a better way than this, but I couldn't think of a summary function.
If you wanted this to be generic (any number if values associated with one id), you could try this:
DECLARE @summary TABLE ([value] varchar(10) , [id] int PRIMARY KEY)
DECLARE @value varchar(10), @id int
INSERT INTO @summary (value, id) SELECT DISTINCT '', id FROM MyTable -- one row per value
DECLARE cur cursor FOR SELECT value, id FROM MyTable
OPEN cur
FETCH NEXT FROM cur INTO @value, @id
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE @summary SET value = value + ',' + @value
FETCH NEXT FROM cur INTO @value, @id
END
CLOSE cur
DEALLOCATE cur
UPDATE @summary SET value = substring(value, 2, len(value)) -- remove first comma
SELECT * FROM @summary
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply