Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2009
    Posts
    17

    Unanswered: How I Merge & Group This data

    Hello All...

    I have table look like this.

    with this scheme :

    NOLPWP,QTY,Code_Product ,Price1,Price2,Price3

    0001/DSO-SPR/OYOK/I/08 1 TNTPE 200000 200000 0
    0001/RAA-SPR/OYOK/I/08 1 TNLGC 75000 33750 41250
    0001/RAA-SPR/OYOK/I/08 2 TPRCN 75000 33750 41250
    0001/SKI-SPV/OSUB-P/I/08 1 TWLCB 0 0 0
    0001/SKI-SPV/OSUB-P/I/08 2 TWLGA 120000 120000 0
    0001/SKI-SPV/OSUB-P/I/08 3 TWLLB 0 0 0
    0001/SKI-SPV/OSUB-P/I/08 4 TWLOB 0 0 0
    0001/MIN-SPV/OBLP/I/08 1 TFTGB 0 0 0
    0001/MIN-SPV/OBLP/I/08 2 TPRGJ 0 0 0
    0001/MIN-SPV/OBLP/I/08 3 LWPEE 0 0 0

    and i want to return result like this (only NOLPW and concatenante on code_product)

    0001/DSO-SPR/OYOK/I/08 TNTPE
    0001/RAA-SPR/OYOK/I/08 TNLGC,TPRCN
    0001/SKI-SPV/OSUB-P/I/08 TWLCB,TWLGA,TWLLB,TWLOB
    0001/MIN-SPV/OBLP/I/08 TFTGB,TPRGJ,LWPEE

    can anyone help me please...coz i need a lot, thanks in advance

    Regards,
    Thomas Bennya

  2. #2
    Join Date
    Dec 2008
    Posts
    135
    DECLARE @TAB table( NOLPWP varchar(32),QTY int,Code_Product varchar(32))
    insert into @tab
    select
    '0001/DSO-SPR/OYOK/I/08', 1 ,'TNTPE' union all select
    '0001/RAA-SPR/OYOK/I/08', 1 ,'TNLGC' union all select
    '0001/RAA-SPR/OYOK/I/08', 2 ,'TPRCN' union all select
    '0001/SKI-SPV/OSUB-P/I/08', 1 ,'TWLCB' union all select
    '0001/SKI-SPV/OSUB-P/I/08', 2 ,'TWLGA' union all select
    '0001/SKI-SPV/OSUB-P/I/08', 3 ,'TWLLB' union all select
    '0001/SKI-SPV/OSUB-P/I/08', 4 ,'TWLOB' union all select
    '0001/MIN-SPV/OBLP/I/08' ,1 ,'TFTGB' union all select
    '0001/MIN-SPV/OBLP/I/08' ,2 ,'TPRGJ' union all select
    '0001/MIN-SPV/OBLP/I/08', 3 ,'LWPEE'



    select distinct NOLPWP,stuff((select distinct ','+ code_product from @tab where nolpwp = p.nolpwp for xml path('')),1,1,'') AS val from @tab p

    works in sql2005 & above

  3. #3
    Join Date
    Feb 2009
    Posts
    17

    Thumbs up U R marvelous...

    ^^ U R Marvelous...thanks for the reply...but unfortunately i used MS-SQL 2K on client, but thanks for the reply..thanks once again Bklr ..any other solution for MS - SQL 2K?

    Regards,
    Thomas Benny

  4. #4
    Join Date
    Dec 2008
    Posts
    135
    hai bny try like this
    DECLARE @TAB table( NOLPWP varchar(32),QTY int,Code_Product varchar(32))
    insert into @tab
    select
    '0001/DSO-SPR/OYOK/I/08', 1 ,'TNTPE' union all select
    '0001/RAA-SPR/OYOK/I/08', 1 ,'TNLGC' union all select
    '0001/RAA-SPR/OYOK/I/08', 2 ,'TPRCN' union all select
    '0001/SKI-SPV/OSUB-P/I/08', 1 ,'TWLCB' union all select
    '0001/SKI-SPV/OSUB-P/I/08', 2 ,'TWLGA' union all select
    '0001/SKI-SPV/OSUB-P/I/08', 3 ,'TWLLB' union all select
    '0001/SKI-SPV/OSUB-P/I/08', 4 ,'TWLOB' union all select
    '0001/MIN-SPV/OBLP/I/08' ,1 ,'TFTGB' union all select
    '0001/MIN-SPV/OBLP/I/08' ,2 ,'TPRGJ' union all select
    '0001/MIN-SPV/OBLP/I/08', 3 ,'LWPEE'

    DECLARE @table TABLE ( Name VARCHAR(MAX),id1 varchar(32))

    DECLARE @str VARCHAR(MAX),@id1 varchar(32)
    SELECT @str = ''

    SELECT @ID1 = MIN(NOLPWP) FROM #temp

    WHILE(@ID1 IS NOT NULL )
    BEGIN
    SELECT @STR = @STR + ','+ Code_Product FROM #temp WHERE @id1= NOLPWP

    SELECT @str = STUFF(@str , 1 , 1,'')

    INSERT INTO @TABLE SELECT @STR,@id1

    SELECT @ID1 = MIN(NOLPWP) FROM #temp WHERE NOLPWP>@ID1

    SELECT @str = ''
    END

    SELECT id1,name FROM @table
    Last edited by bklr; 02-11-09 at 08:09.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You could also make a user-defined function (UDF) to do this
    Code:
    CREATE TABLE dbo.tab ( NOLPWP varchar(32),QTY int,Code_Product varchar(32))
    insert into dbo.tab
    select
    '0001/DSO-SPR/OYOK/I/08', 1 ,'TNTPE' union all select
    '0001/RAA-SPR/OYOK/I/08', 1 ,'TNLGC' union all select
    '0001/RAA-SPR/OYOK/I/08', 2 ,'TPRCN' union all select
    '0001/SKI-SPV/OSUB-P/I/08', 1 ,'TWLCB' union all select
    '0001/SKI-SPV/OSUB-P/I/08', 2 ,'TWLGA' union all select
    '0001/SKI-SPV/OSUB-P/I/08', 3 ,'TWLLB' union all select
    '0001/SKI-SPV/OSUB-P/I/08', 4 ,'TWLOB' union all select 
    '0001/MIN-SPV/OBLP/I/08' ,1 ,'TFTGB' union all select
    '0001/MIN-SPV/OBLP/I/08' ,2 ,'TPRGJ' union all select
    '0001/MIN-SPV/OBLP/I/08', 3 ,'LWPEE' 
    
    GO
    
    CREATE FUNCTION dbo.concat_tab_products (
       @nolpwp varchar(32)
    )
      RETURNS varchar(1000)
    AS
      BEGIN
        DECLARE @return varchar(1000)
    
        SELECT @return = Coalesce(@return + ',', '') + code_product
        FROM   dbo.tab
        WHERE  nolpwp = @nolpwp
    
        RETURN @return
      END
    GO
    
    SELECT *
         , dbo.concat_tab_products(nolpwp)
    FROM  dbo.tab
    
    GO
    DROP FUNCTION dbo.concat_tab_products
    DROP TABLE dbo.tab
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2009
    Posts
    17

    Thumbs up

    Many Thanks to @bklr and @georgev you guys do a great help..and thanks again...wish you both always bless and support people blind like me....hehe =))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •