You must Sign In to post a response.
  • Category: SQL Server

    Merge two rows into one

    Hi all,

    I want this result set to be merged into one row as in sql query:

    Result set to be merged:
    412 NULL NULL NULL Koleckar, Andreas Khasraghi, Jasmina Schubert, Julia 6
    412 Koleckar, Andreas Schubert, Julia Khasraghi, Jasmina NULL NULL NULL 1


    Required row set:
    412 Koleckar, Andreas Schubert, Julia Khasraghi, Jasmina Koleckar, Andreas Khasraghi, Jasmina Schubert, Julia 7

    Please help me here

    Thanks
  • #769164
    Hi,

    So you have two result set with you. Take those two result set in the form of LINQ and perform the scripting like below.


    var query1 = from sn in code
    group sn by sn.Substring(0, 10) into g
    select new
    {
    Key = g.Key,
    Cnt = g.Count(),
    Min = g.Min(v => v.Substring(10, 4)),
    Max = g.Max(v => v.Substring(10, 4))
    };
    var query2 = from sn1 in codes
    group sn1 by sn1.Substring(0, 11) into g
    select new
    {
    Key = g.Key,
    Cnt = g.Count(),
    Min = g.Min(v => v.Substring(11, 4)),
    Max = g.Max(v => v.Substring(11, 4))
    };
    var query3= query1.Union(query2)



    So in Query3 you have both the result set in two rows.
    So you can take both the rows, when the first row is null take the second row value, if second row also null then assign it as null itself.

    Thanks,
    Mani

  • #769168
    Hi,

    I assume that 412 is your id and rest of the columns I assumed that is col1, col2, col3 respectively.

    You can use SUM function and group it based on ID like below in your sql server.


    SELECT ID, SUM(COL1),SUM(COL2), SUM(COL3)...
    FROM TableName
    Where Conditions
    GROUP BY ID


    Hope this helps you....

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #769186
    You can use given code snippet to Merge two rows into one in SQL Query


    Select
    EmployeeID,
    Active.IndividualPay As IndPay_IsActive,
    Active.FamilyPay As FamilyPay_IsActive,
    Inactive.IndividualPay As IndPay_IsNotActive,
    Inactive.FamilyPay As FamilyPay_IsNotActive
    From
    PayTable Active
    Join PayTable Inactive On Active.EmployeeID = Inactive.EmployeeId
    And Inactive.IsActive = 'false'
    Where
    Active.IsActive = 'true'


    Useful Reference : http://stackoverflow.com/questions/8005846/sql-server-combining-multiple-rows-into-one-row


Sign In to post your comments