Solved

How to split single row to multi row

Posted on 2010-09-15
4
438 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

777 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