Solved

How to split single row to multi row

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now