Creating a record for each field in a row? Can it be done?

Creating a record for each field in a row? Can it be done?

Post by Chuma » Sat, 14 Sep 2002 00:33:15



I've got a a table that looks similar to this:

Desc, Jan, Feb, Mar, Apr, May, June, July, Aug, Sep, Oct, Nov, Dec

The month fields contain dollar amounts.  I need to get the dollar amounts
into a single column and each in their own record. The data will be imported
from one sql db to another one using sql -- so I'm hoping for a way to
accomplish this using an sproc or a sql statement. Let's say I have a data
set that looks like this:

Record 1: Outside Sales, 100, 400, 300, 400, 500, 600, 200, 100, 200, 100,
50, 500
Record 2: Intrasales, 200, 200, 200, 300, 150, 700, 100, 0, 100, 200, 200,
100

I want to convert it to look like this dataset (Desc, amount, month):

Record 1: Outside sales, 100, Jan
Record 2: Outside sales, 400, Feb
Record 3: Outside sales, 500, Mar
Record 4: Outside sales, 600, Apr
etc

Is there a way I could accomplish this using a sql statement?

Thanks for any help or advice.

Ry

 
 
 

Creating a record for each field in a row? Can it be done?

Post by Anith Se » Sat, 14 Sep 2002 01:16:41


You can try something along the lines of...

SELECT Desc,
       CASE Num
            WHEN 1 THEN Amount
            WHEN 2 THEN Amount
            WHEN 3 THEN Amount
            WHEN 4 THEN Amount
            WHEN 5 THEN Amount
            WHEN 6 THEN Amount
            WHEN 7 THEN Amount
            WHEN 8 THEN Amount
            WHEN 9 THEN Amount
            WHEN 10 THEN Amount
            WHEN 11 THEN Amount
            WHEN 12 THEN Amount
       END,
       DATENAME(m, '1900-' + CAST(num AS VARCHAR) + '-01')
       FROM yourTable
 CROSS JOIN (SELECT 1 UNION ALL
             SELECT 2 UNION ALL
             SELECT 3 UNION ALL
             SELECT 4 UNION ALL
             SELECT 5 UNION ALL
             SELECT 6 UNION ALL
             SELECT 7 UNION ALL
             SELECT 8 UNION ALL
             SELECT 9 UNION ALL
             SELECT 10 UNION ALL
             SELECT 11 UNION ALL
             SELECT 12) Number(Num)

Normalize your tables, add keys and make sure a column does
not have multiple values. If this problem exists in multiple
tables, create a number table and clean up the data. If you post
your DDLs & sample data , someone can show you how to do it...

--
- Anith

 
 
 

Creating a record for each field in a row? Can it be done?

Post by oj » Sat, 14 Sep 2002 14:52:44


If you want to roll your own sql check out:

If you know in advance the number of columns in the result set, try:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q175574.

If you have a variable number of columns, try:
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=15608

If you want some interesting utilities to do it for you (work smart:)

http://www.ag-software.com/AGS/xp ags crosstab.asp

RAC at:
www.rac4sql.net

--
-oj
http://www.rac4sql.net


Quote:> I've got a a table that looks similar to this:

> Desc, Jan, Feb, Mar, Apr, May, June, July, Aug, Sep, Oct, Nov, Dec

> The month fields contain dollar amounts.  I need to get the dollar amounts
> into a single column and each in their own record. The data will be
imported
> from one sql db to another one using sql -- so I'm hoping for a way to
> accomplish this using an sproc or a sql statement. Let's say I have a data
> set that looks like this:

> Record 1: Outside Sales, 100, 400, 300, 400, 500, 600, 200, 100, 200, 100,
> 50, 500
> Record 2: Intrasales, 200, 200, 200, 300, 150, 700, 100, 0, 100, 200, 200,
> 100

> I want to convert it to look like this dataset (Desc, amount, month):

> Record 1: Outside sales, 100, Jan
> Record 2: Outside sales, 400, Feb
> Record 3: Outside sales, 500, Mar
> Record 4: Outside sales, 600, Apr
> etc

> Is there a way I could accomplish this using a sql statement?

> Thanks for any help or advice.

> Ry

 
 
 

1. Field Validation - What am I doing wrong ?

I've just started in VFP.

I browse a dbf and everthing works fine until I have a field valid routine try to update
another field, then I get an error saying that I can't update the cursor because it's designed
for read only.

fields:
first1  c(20)   valid upd_name()
last2   c(20)   valid upd_name()
first2  c(20)   valid upd_name()
last2   c(20)   valid upd_name()
name    c(60)

procedure upd_name

local lcName

lcName = alltrim(last1) + ', ' + alltrim(first1)
if !empty(first2+last2)
  lcName = lcName + ' and'
  if last2<>last1
    lcName = lcName + alltrim(last2) + ','
  endif
  lcName = lcName + ' ' + alltrim(first2)
  if name <> lcName
    replace name with lcName            && this is the line that crashes
    ** if I leave this line out everything,s fine, all other fields get updated
    ** examples of lcName:  (1) Smith, John and Mary (2) Smith, John and Doe, Mary
  endif
endif

TIA

Bill

2. Can I read a query from another query in my project?

3. creating a table of pairs of record IDs for records with similar content in text field

4. Storing Jpegs - is it possible?

5. What am I forgetting or doing wrong

6. Missing Info

7. Am I doing this the best way?

8. Slow connect times

9. what am I doing wrong in this procedure?

10. TSQL question - what am I doing wrong?

11. what am i doing wrong?

12. Am I doing this correctly?

13. Simple one what am i doing wrong