Separation

  • 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

  • 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

  • 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