Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2012
    Posts
    8

    Unanswered: Split single row to multiple rows

    Hi ,

    I have a table which looks like

    Low High
    -------------------------
    cx01 cx04
    sn05 sn08

    I need output like

    Result
    -------------
    cx01
    cx02
    cx03
    cx04
    sn05
    sn06
    sn07
    sn08

    Can someone help me to get this output using sql query?

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    SELECT Low as result
    FROM MyTable
     UNION ALL
    SELECT High
    FROM MyTable
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Feb 2012
    Posts
    8
    Hi Wim,

    It didnt bring the desired output.
    The output of your query is

    cx01
    sn05
    cx04
    sn08

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I didn't realise you have no SQL knowledge at all. Ordering records is quite basic.

    This will sort the records in alphabetic order:
    Code:
    SELECT Low as Result
    FROM MyTable
     UNION ALL
    SELECT High
    FROM MyTable
    ORDER BY 1 ASC
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Feb 2012
    Posts
    8
    My question is to generate the intermediate rows.
    My output should be

    cx01
    cx02
    cx03
    cx04
    sn05
    sn06
    sn07
    sn08

    But your query result is
    cx01
    cx04
    sn05
    sn06

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I'm sorry. Only now I realise what your question is.

    This should work:
    Code:
    CREATE TABLE #DaTable(
    	Low	VARCHAR(10),
    	High	VARCHAR(10)
    )
    INSERT INTO #DaTable(Low, High) VALUES
    ('cx01', 'cx04'), 
    ('sn05', 'sn08')
    
    WITH CTE AS
    (SELECT LEFT(Low, 2) as AphaPart, CAST(RIGHT(Low, 2) AS INT ) AS NumPart, Low, High, 'L' as isHighLow
    FROM #DaTable
    	UNION ALL
    SELECT LEFT(High, 2) as AphaPart, CAST(RIGHT(High, 2) AS INT ) AS NumPart, Low, High, 'H' as isHighLow
    FROM #DaTable
    ),
    MyTable_ AS
    (SELECT aphaPart, NumPart, Low, High
    FROM CTE
    WHERE isHighLow = 'L'
    	UNION ALL
    SELECT MyTable_.aphaPart, MyTable_.NumPart + 1, MyTable_.Low, MyTable_.High
    FROM MyTable_ 
    	INNER JOIN CTE ON
    		MyTable_.Low = CTE.Low AND
    		MyTable_.High = CTE.High
    WHERE CTE.isHighLow = 'H' AND
    	MyTable_.NumPart + 1 <= CTE.NumPart
    )
    SELECT aphaPart + RIGHT('00' + CAST(NumPart AS VARCHAR(2)), 2) as Result
    FROM MyTable_
    ORDER BY Low, High
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Feb 2012
    Posts
    8

    Cool

    Quote Originally Posted by Wim View Post
    I'm sorry. Only now I realise what your question is.

    This should work:
    Code:
    CREATE TABLE #DaTable(
    	Low	VARCHAR(10),
    	High	VARCHAR(10)
    )
    INSERT INTO #DaTable(Low, High) VALUES
    ('cx01', 'cx04'), 
    ('sn05', 'sn08')
    
    WITH CTE AS
    (SELECT LEFT(Low, 2) as AphaPart, CAST(RIGHT(Low, 2) AS INT ) AS NumPart, Low, High, 'L' as isHighLow
    FROM #DaTable
    	UNION ALL
    SELECT LEFT(High, 2) as AphaPart, CAST(RIGHT(High, 2) AS INT ) AS NumPart, Low, High, 'H' as isHighLow
    FROM #DaTable
    ),
    MyTable_ AS
    (SELECT aphaPart, NumPart, Low, High
    FROM CTE
    WHERE isHighLow = 'L'
    	UNION ALL
    SELECT MyTable_.aphaPart, MyTable_.NumPart + 1, MyTable_.Low, MyTable_.High
    FROM MyTable_ 
    	INNER JOIN CTE ON
    		MyTable_.Low = CTE.Low AND
    		MyTable_.High = CTE.High
    WHERE CTE.isHighLow = 'H' AND
    	MyTable_.NumPart + 1 <= CTE.NumPart
    )
    SELECT aphaPart + RIGHT('00' + CAST(NumPart AS VARCHAR(2)), 2) as Result
    FROM MyTable_
    ORDER BY Low, High
    Thank You MASTER.
    It really works.
    Thanks again for sharing this query.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by dineshkumarsiva View Post
    Thank You MASTER.
    I would say that this response rates another

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by Pat Phelan
    I would say that this response rates another

    -PatP
    Pat,

    I don't really feel 100% comfortable with the idea that the beer didn't came from the one who started the thread. But I've been dragging this unlucky number of beers for long enough, so I eagerly accept. Thanks!

    Earned beers: 14
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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