Solved

How to split single row to multi row

Posted on 2010-09-15
4
439 Views
Last Modified: 2013-11-05
Hi Experts,
I want to split data from one row to multi rows

For example, I have a table with this columns and this data

COD   BEGIN    END
------------------------
ABC    3           5
BHG    4           7

I would like to create a query to return

COD   ITEM
--------------
ABC    3
ABC    4
ABC    5
BHG    4
BHG    5
BHG    6
BHG    7

Can you help me about this?
Thanks in advance
Leopoldo



0
Comment
Question by:onoratoInformatica
4 Comments
 
LVL 6

Expert Comment

by:Mike D
ID: 33680486
Hi onoratoInformatica,

I'm pretty sure you will need some post processing (with php, asp, etc) after your db query.
Select your rows into a variable and iterate through the columns and reformat accordingly.

Hope this helps,

JD
0
 
LVL 4

Expert Comment

by:tabish
ID: 33680981
Either you have to do what johndoeherty suggested or you'll have to write a Stored Procedure to achieve that if you want to share it at multiple front ends.
0
 
LVL 22

Accepted Solution

by:
pivar earned 500 total points
ID: 33681196
Hi,

If you're on SQL 2005 you can try:


WITH CTE AS (
      SELECT COD, [BEGIN] AS ITEM
            FROM TABLENAME
      UNION ALL
      SELECT t.COD, C.ITEM+1 AS ITEM
            FROM TABLENAME T
                  JOIN CTE C ON C.COD = T.COD AND T.[END] > C.ITEM
)
SELECT * FROM CTE ORDER BY COD;

/peter
0
 

Author Closing Comment

by:onoratoInformatica
ID: 33681474
Thanks, that's what I tried
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question