Post reply

Merge Many rows to one row

  • July 5, 2009 at 12:20 am

    #202025

    Hello All;

    I want to create a view continas a customer ID and the services he is allowed to use. The current view it will be as follow:

    Customer ID: Customer Services:

    1234 Football

    1234 Swimming

    1234 Table Tennies

    1234 Basketball

    Now i dont want to have so many rows linked to 1 customer. So i have created a new table and give each activity a unique number:

    Football 2

    Swimming 4

    Table Tennies 8

    Basketball 16

    So the new view should be like:

    Customer_ID Acitivities

    1234 30 (The sum of all the above activities).

    or if it possible:

    Customer_ID Activities:

    1234 ABCD

    Where A=Football

    B= Swimming

    C= Table Tennies

    D= BasketBall

    Thanks in Advance

  • July 5, 2009 at 1:09 am

    #1020034

    Hi,

    take a look at this articles http://qa.sqlservercentral.com/articles/XML/66932/

    and

    http://qa.sqlservercentral.com/articles/T-SQL/62867/

  • July 5, 2009 at 1:15 am

    #1020035

    I don't know what a split function is needed for here...

    For concatenation, see this topic

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

    N 56°04'39.16"
    E 12°55'05.25"

  • July 5, 2009 at 2:15 am

    #1020036

    oops, of course, my fault, just made the split my self and was confused :blush:

  • July 6, 2009 at 5:10 am

    #1020287

    Hello Kupy & Peso;

    Thanks for your reply and support .. Well i have read the articles and gone through it ,,, but i cant really have a clear idea, maybe because iam still in a begginer level 🙂

    However, will keep on searching in the net, if you manage to find another article, please post it here.

    Thanks again 🙂

  • July 6, 2009 at 5:24 am

    #1020291

    Hi,

    also try this

    create table #temp

    (

    Cust_Id int,

    Cust_Ser varchar(20)

    )

    insert into #temp

    select 1234,'Football'

    union all

    select 1234,'Swimming'

    union all

    select 1234,'Table Tennies'

    union all

    select 1234,'Basketball'

    select Cust_Id, (case when Cust_Ser = 'Football' then 'FB'

    when Cust_Ser = 'Swimming' then 'SW'

    when Cust_Ser = 'Table Tennies' then 'TT'

    when Cust_Ser = 'Basketball' then 'BB' end)Cust_Ser

    into #temp1

    from #temp

    declare @RESULT nvarchar(1000)

    select @RESULT=coalesce(@RESULT,'')+ Cust_Ser from #temp1

    select distinct Cust_Id, @RESULT Cust_Ser from #temp1

    ARUN SAS

  • July 6, 2009 at 7:59 am

    #1020391

    For concatenation u can use FOR XML PATH() to concatenate rows to to a single column

    Eralper
    SQL Server and T-SQL Tutorials and Articles
    Microsoft Certification and Certification Exams

  • July 7, 2009 at 5:06 am

    #1020959

    Hello Arun;

    Thanks for your response men 🙂

    Well the table is already exist in the database and i dont need to insert any new records ,, i need to create a view arrange the output as i have mentioned above. There is more then 55,000 records on the table and i want to reduce this number by relating many services to one customer_ID. for Example:

    Customer ID: Customer No: Facilities:

    555 1255599 Football

    555 1255534 Swimming

    555 1255599 Swimming

    555 1255512 BasketBall

    555 1255511 Swimming

    555 1255534 Football

    Where Customer_ID is a unique number for the customer. Now i want it to be in this way:

    Customer_ID: Facilities:

    555 Football

    555 Swimming

    555 Basketball

    Any idea or article will help?

    Thanks in Advance.

  • July 7, 2009 at 9:17 am

    #1021145

    Bur@ir (7/5/2009)

    Now i dont want to have so many rows linked to 1 customer.

    Why not? It's what databases do. The other methods will require the additional headaches of either concatenation or bitwise math.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • July 7, 2009 at 9:09 pm

    #1021448

    Bur@ir (7/7/2009)

    Where Customer_ID is a unique number for the customer. Now i want it to be in this way:

    Customer_ID: Facilities:

    555 Football

    555 Swimming

    555 Basketball

    Hi,

    select distinct Customer_ID ,Facilities from MyTable

    ARUN SAS

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply