Post reply

Creating 2 Rows from 2 fields currently in the same row.

  • February 28, 2011 at 9:34 am

    #234487

    Hi All,

    Hope you can help me over a brick wall I've come up against!

    I currently have 1 row per account. In this row I have 2 fields called CustomerID and CustomerID2. CustomerID will always have a value, CustomerID2 may not. What I want to is to create 2 rows if both fields have a value and only 1 if not. So just to be clear:-

    Account CustomerID CustomerID2

    1 8888 9999

    2 7777

    I would want to see as

    Account CustomerID

    1 8888

    1 9999

    2 7777

    Any ideas how I could go about achieving this would be very much appreciated.

    Thanks,

    Wardy

  • February 28, 2011 at 9:45 am

    #1292412

    How does this work? (Also... please read the article in the first link in my signature for better ways to post to help us help you - it also makes it where more volunteers here would be willing to help you out.)

    DECLARE @test-2 TABLE (Account INT, CustomerID INT, CustomerID2 INT NULL);

    INSERT INTO @test-2 (Account, CustomerID, CustomerID2) VALUES (1, 8888, 9999);

    INSERT INTO @test-2 (Account, CustomerID) VALUES (2, 7777);

    WITH CTE AS

    (

    SELECT Account, CustomerID, RowType = 1

    FROM @test-2

    UNION ALL

    SELECT Account, CustomerID2, RowType = 2

    FROM @test-2

    WHERE CustomerID2 IS NOT NULL

    )

    SELECT Account, CustomerID

    FROM CTE

    ORDER BY Account, RowType;

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • February 28, 2011 at 11:29 am

    #1292482

    Many thanks Wayne and will read the article

    Wardy

Viewing 3 posts - 1 through 2 (of 2 total)

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