Link to home
Start Free TrialLog in
Avatar of Connie Jerdet-Skehan
Connie Jerdet-SkehanFlag for United States of America

asked on

add 1 to a field for 100 rows

I am using sql server 2014

I have a temp table

Folder      casefile
1                002774
2                084936
3                062810
4                002793
5                243840

I would like to create a table that would add 1 for 100 rows group by "folder".  There are only 5 "folders". Is there a way to go about doing this.  I would like results to look like one of the following.

   1           2        3              4            5
2775   84936  62810    2793  243840
2776   84937  62811    2794  243841
2777   84938  62812    2795  243842
2778   84939  62813    2796  243843 etc

*** Or something like
1 2775
1 2776
1 2777
1 2778 etc
2 84936
2 84937
2 84938
2 84939 etc

I am new to sql and have some ms access vba coding knowledge. Please explain steps. If this can be accomplished.
Avatar of JesterToo
JesterToo
Flag of United States of America image

You may need to explain in more detail... I don't see any relationship between your question, the data structure you already have, and the resulting structure you showed.
Avatar of Connie Jerdet-Skehan

ASKER

The first table shows the folder number and the last casefile for that folder number.

I want to create a table that adds 100 casefile number for each foldernumber starting with the last casefile number in increments of one.

I just don't know how I would go about doing it.

Does that make more sense??
I think so.  What tools do you have available for creating a new table and populating it?  And do you have "create table" rights in the database?
Also, what table name and column names do you want for the new table?
Tool?? I have all rights to MS sql. I am the administrator of it. Learning by example. :)
Column names **** Folders, New Casefiles *** and whatever else maybe needed.
Avatar of PortletPaul
You can always use a CROSS JOIN to deliberately multiply rows. Here you can dynamically build a "tally table" of numbers 0 to 99 for that cross join.
CREATE TABLE Table1
    ([Folder] int, [casefile] varchar(20))
;
    
INSERT INTO Table1
    ([Folder], [casefile])
VALUES
    (1, '002774'),
    (2, '084936'),
    (3, '062810'),
    (4, '002793'),
    (5, '243840')
;

;WITH
Digits AS (
          SELECT 0 AS digit UNION ALL
          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
          )
, Tally AS (
          SELECT [tens].digit * 10 + [ones].digit AS number
          FROM Digits [ones]
          CROSS JOIN Digits [tens]
          )
select
   t.folder, t.casefile + cast(tally.number+1 as varchar(20)) as new_casefile
from table1 t
cross join tally
order by t.folder, tally.number

Open in new window

Please see the full results at: http://rextester.com/ZEOZRS23183

result will look like this:
+-----+--------+--------------+
|     | folder | new_casefile |
+-----+--------+--------------+
|   1 |      1 |      0027741 |
|   2 |      1 |      0027742 |
|   3 |      1 |      0027743 |
|   4 |      1 |      0027744 |
|   5 |      1 |      0027745 |
|   6 |      1 |      0027746 |
|   7 |      1 |      0027747 |
|   8 |      1 |      0027748 |
|   9 |      1 |      0027749 |
|  10 |      1 |     00277410 |
|  11 |      1 |     00277411 |
|  12 |      1 |     00277412 |
|  13 |      1 |     00277413 |
|  14 |      1 |     00277414 |
|  15 |      1 |     00277415 |
|  16 |      1 |     00277416 |
|  17 |      1 |     00277417 |
|  18 |      1 |     00277418 |
|  19 |      1 |     00277419 |
|  20 |      1 |     00277420 |
|  21 |      1 |     00277421 |
|  22 |      1 |     00277422 |
|  23 |      1 |     00277423 |
|  24 |      1 |     00277424 |
|  25 |      1 |     00277425 |
|  26 |      1 |     00277426 |
|  27 |      1 |     00277427 |
|  28 |      1 |     00277428 |
|  29 |      1 |     00277429 |
|  30 |      1 |     00277430 |
|  31 |      1 |     00277431 |
|  32 |      1 |     00277432 |
|  33 |      1 |     00277433 |
|  34 |      1 |     00277434 |
|  35 |      1 |     00277435 |
|  36 |      1 |     00277436 |
|  37 |      1 |     00277437 |
|  38 |      1 |     00277438 |
|  39 |      1 |     00277439 |
|  40 |      1 |     00277440 |
|  41 |      1 |     00277441 |
|  42 |      1 |     00277442 |
|  43 |      1 |     00277443 |
|  44 |      1 |     00277444 |
|  45 |      1 |     00277445 |
|  46 |      1 |     00277446 |
|  47 |      1 |     00277447 |
|  48 |      1 |     00277448 |
|  49 |      1 |     00277449 |
|  50 |      1 |     00277450 |
|  51 |      1 |     00277451 |
|  52 |      1 |     00277452 |
|  53 |      1 |     00277453 |
|  54 |      1 |     00277454 |
|  55 |      1 |     00277455 |
|  56 |      1 |     00277456 |
|  57 |      1 |     00277457 |
|  58 |      1 |     00277458 |
|  59 |      1 |     00277459 |
|  60 |      1 |     00277460 |
|  61 |      1 |     00277461 |
|  62 |      1 |     00277462 |
|  63 |      1 |     00277463 |
|  64 |      1 |     00277464 |
|  65 |      1 |     00277465 |
|  66 |      1 |     00277466 |
|  67 |      1 |     00277467 |
|  68 |      1 |     00277468 |
|  69 |      1 |     00277469 |
|  70 |      1 |     00277470 |
|  71 |      1 |     00277471 |
|  72 |      1 |     00277472 |
|  73 |      1 |     00277473 |
|  74 |      1 |     00277474 |
|  75 |      1 |     00277475 |
|  76 |      1 |     00277476 |
|  77 |      1 |     00277477 |
|  78 |      1 |     00277478 |
|  79 |      1 |     00277479 |
|  80 |      1 |     00277480 |
|  81 |      1 |     00277481 |
|  82 |      1 |     00277482 |
|  83 |      1 |     00277483 |
|  84 |      1 |     00277484 |
|  85 |      1 |     00277485 |
|  86 |      1 |     00277486 |
|  87 |      1 |     00277487 |
|  88 |      1 |     00277488 |
|  89 |      1 |     00277489 |
|  90 |      1 |     00277490 |
|  91 |      1 |     00277491 |
|  92 |      1 |     00277492 |
|  93 |      1 |     00277493 |
|  94 |      1 |     00277494 |
|  95 |      1 |     00277495 |
|  96 |      1 |     00277496 |
|  97 |      1 |     00277497 |
|  98 |      1 |     00277498 |
|  99 |      1 |     00277499 |
| 100 |      1 |    002774100 |
| 101 |      2 |      0849361 |
| 102 |      2 |      0849362 |
| 103 |      2 |      0849363 |
+-----+--------+--------------+

Open in new window

This variant will perform a "pivot"
CREATE TABLE Table1
    ([Folder] int, [casefile] varchar(20))
;
    
INSERT INTO Table1
    ([Folder], [casefile])
VALUES
    (1, '002774'),
    (2, '084936'),
    (3, '062810'),
    (4, '002793'),
    (5, '243840')
;

;WITH
Digits AS (
          SELECT 0 AS digit UNION ALL
          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
          )
, Tally AS (
          SELECT [tens].digit * 10 + [ones].digit AS number
          FROM Digits [ones]
          CROSS JOIN Digits [tens]
          )
select 
   number, [1], [2], [3], [4], [5]
from (
        select
           t.folder, tally.number + 1 number, (try_cast(t.casefile as int) + tally.number + 1) as new_casefile
        from table1 t
        cross join tally
     ) d
pivot ( max(new_casefile) for folder IN([1],[2],[3],[4],[5]) ) p

Open in new window

result:
+-----+--------+------+-------+-------+------+--------+
|     | number |  1   |   2   |   3   |  4   |   5    |
+-----+--------+------+-------+-------+------+--------+
|   1 |      1 | 2775 | 84937 | 62811 | 2794 | 243841 |
|   2 |      2 | 2776 | 84938 | 62812 | 2795 | 243842 |
|   3 |      3 | 2777 | 84939 | 62813 | 2796 | 243843 |
|   4 |      4 | 2778 | 84940 | 62814 | 2797 | 243844 |
|   5 |      5 | 2779 | 84941 | 62815 | 2798 | 243845 |
|   6 |      6 | 2780 | 84942 | 62816 | 2799 | 243846 |
|   7 |      7 | 2781 | 84943 | 62817 | 2800 | 243847 |
|   8 |      8 | 2782 | 84944 | 62818 | 2801 | 243848 |
|   9 |      9 | 2783 | 84945 | 62819 | 2802 | 243849 |
|  10 |     10 | 2784 | 84946 | 62820 | 2803 | 243850 |
|  11 |     11 | 2785 | 84947 | 62821 | 2804 | 243851 |
|  12 |     12 | 2786 | 84948 | 62822 | 2805 | 243852 |
|  13 |     13 | 2787 | 84949 | 62823 | 2806 | 243853 |
|  14 |     14 | 2788 | 84950 | 62824 | 2807 | 243854 |
|  15 |     15 | 2789 | 84951 | 62825 | 2808 | 243855 |
|  16 |     16 | 2790 | 84952 | 62826 | 2809 | 243856 |
|  17 |     17 | 2791 | 84953 | 62827 | 2810 | 243857 |
|  18 |     18 | 2792 | 84954 | 62828 | 2811 | 243858 |
|  19 |     19 | 2793 | 84955 | 62829 | 2812 | 243859 |
|  20 |     20 | 2794 | 84956 | 62830 | 2813 | 243860 |
|  21 |     21 | 2795 | 84957 | 62831 | 2814 | 243861 |
|  22 |     22 | 2796 | 84958 | 62832 | 2815 | 243862 |
|  23 |     23 | 2797 | 84959 | 62833 | 2816 | 243863 |
|  24 |     24 | 2798 | 84960 | 62834 | 2817 | 243864 |
|  25 |     25 | 2799 | 84961 | 62835 | 2818 | 243865 |
|  26 |     26 | 2800 | 84962 | 62836 | 2819 | 243866 |
|  27 |     27 | 2801 | 84963 | 62837 | 2820 | 243867 |
|  28 |     28 | 2802 | 84964 | 62838 | 2821 | 243868 |
|  29 |     29 | 2803 | 84965 | 62839 | 2822 | 243869 |
|  30 |     30 | 2804 | 84966 | 62840 | 2823 | 243870 |
|  31 |     31 | 2805 | 84967 | 62841 | 2824 | 243871 |
|  32 |     32 | 2806 | 84968 | 62842 | 2825 | 243872 |
|  33 |     33 | 2807 | 84969 | 62843 | 2826 | 243873 |
|  34 |     34 | 2808 | 84970 | 62844 | 2827 | 243874 |
|  35 |     35 | 2809 | 84971 | 62845 | 2828 | 243875 |
|  36 |     36 | 2810 | 84972 | 62846 | 2829 | 243876 |
|  37 |     37 | 2811 | 84973 | 62847 | 2830 | 243877 |
|  38 |     38 | 2812 | 84974 | 62848 | 2831 | 243878 |
|  39 |     39 | 2813 | 84975 | 62849 | 2832 | 243879 |
|  40 |     40 | 2814 | 84976 | 62850 | 2833 | 243880 |
|  41 |     41 | 2815 | 84977 | 62851 | 2834 | 243881 |
|  42 |     42 | 2816 | 84978 | 62852 | 2835 | 243882 |
|  43 |     43 | 2817 | 84979 | 62853 | 2836 | 243883 |
|  44 |     44 | 2818 | 84980 | 62854 | 2837 | 243884 |
|  45 |     45 | 2819 | 84981 | 62855 | 2838 | 243885 |
|  46 |     46 | 2820 | 84982 | 62856 | 2839 | 243886 |
|  47 |     47 | 2821 | 84983 | 62857 | 2840 | 243887 |
|  48 |     48 | 2822 | 84984 | 62858 | 2841 | 243888 |
|  49 |     49 | 2823 | 84985 | 62859 | 2842 | 243889 |
|  50 |     50 | 2824 | 84986 | 62860 | 2843 | 243890 |
|  51 |     51 | 2825 | 84987 | 62861 | 2844 | 243891 |
|  52 |     52 | 2826 | 84988 | 62862 | 2845 | 243892 |
|  53 |     53 | 2827 | 84989 | 62863 | 2846 | 243893 |
|  54 |     54 | 2828 | 84990 | 62864 | 2847 | 243894 |
|  55 |     55 | 2829 | 84991 | 62865 | 2848 | 243895 |
|  56 |     56 | 2830 | 84992 | 62866 | 2849 | 243896 |
|  57 |     57 | 2831 | 84993 | 62867 | 2850 | 243897 |
|  58 |     58 | 2832 | 84994 | 62868 | 2851 | 243898 |
|  59 |     59 | 2833 | 84995 | 62869 | 2852 | 243899 |
|  60 |     60 | 2834 | 84996 | 62870 | 2853 | 243900 |
|  61 |     61 | 2835 | 84997 | 62871 | 2854 | 243901 |
|  62 |     62 | 2836 | 84998 | 62872 | 2855 | 243902 |
|  63 |     63 | 2837 | 84999 | 62873 | 2856 | 243903 |
|  64 |     64 | 2838 | 85000 | 62874 | 2857 | 243904 |
|  65 |     65 | 2839 | 85001 | 62875 | 2858 | 243905 |
|  66 |     66 | 2840 | 85002 | 62876 | 2859 | 243906 |
|  67 |     67 | 2841 | 85003 | 62877 | 2860 | 243907 |
|  68 |     68 | 2842 | 85004 | 62878 | 2861 | 243908 |
|  69 |     69 | 2843 | 85005 | 62879 | 2862 | 243909 |
|  70 |     70 | 2844 | 85006 | 62880 | 2863 | 243910 |
|  71 |     71 | 2845 | 85007 | 62881 | 2864 | 243911 |
|  72 |     72 | 2846 | 85008 | 62882 | 2865 | 243912 |
|  73 |     73 | 2847 | 85009 | 62883 | 2866 | 243913 |
|  74 |     74 | 2848 | 85010 | 62884 | 2867 | 243914 |
|  75 |     75 | 2849 | 85011 | 62885 | 2868 | 243915 |
|  76 |     76 | 2850 | 85012 | 62886 | 2869 | 243916 |
|  77 |     77 | 2851 | 85013 | 62887 | 2870 | 243917 |
|  78 |     78 | 2852 | 85014 | 62888 | 2871 | 243918 |
|  79 |     79 | 2853 | 85015 | 62889 | 2872 | 243919 |
|  80 |     80 | 2854 | 85016 | 62890 | 2873 | 243920 |
|  81 |     81 | 2855 | 85017 | 62891 | 2874 | 243921 |
|  82 |     82 | 2856 | 85018 | 62892 | 2875 | 243922 |
|  83 |     83 | 2857 | 85019 | 62893 | 2876 | 243923 |
|  84 |     84 | 2858 | 85020 | 62894 | 2877 | 243924 |
|  85 |     85 | 2859 | 85021 | 62895 | 2878 | 243925 |
|  86 |     86 | 2860 | 85022 | 62896 | 2879 | 243926 |
|  87 |     87 | 2861 | 85023 | 62897 | 2880 | 243927 |
|  88 |     88 | 2862 | 85024 | 62898 | 2881 | 243928 |
|  89 |     89 | 2863 | 85025 | 62899 | 2882 | 243929 |
|  90 |     90 | 2864 | 85026 | 62900 | 2883 | 243930 |
|  91 |     91 | 2865 | 85027 | 62901 | 2884 | 243931 |
|  92 |     92 | 2866 | 85028 | 62902 | 2885 | 243932 |
|  93 |     93 | 2867 | 85029 | 62903 | 2886 | 243933 |
|  94 |     94 | 2868 | 85030 | 62904 | 2887 | 243934 |
|  95 |     95 | 2869 | 85031 | 62905 | 2888 | 243935 |
|  96 |     96 | 2870 | 85032 | 62906 | 2889 | 243936 |
|  97 |     97 | 2871 | 85033 | 62907 | 2890 | 243937 |
|  98 |     98 | 2872 | 85034 | 62908 | 2891 | 243938 |
|  99 |     99 | 2873 | 85035 | 62909 | 2892 | 243939 |
| 100 |    100 | 2874 | 85036 | 62910 | 2893 | 243940 |
+-----+--------+------+-------+-------+------+--------+

Open in new window

@PortletPaul

The pivot table produces the results I need. Now how would i store those results into a table so the format looks like

Folder  casefile
1           2775
1           2776
1           2777
2          84937
2          84938
2         84939   I didn't use all the numbers but I am sure you get what I am looking for.
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you so much this is perfect.
Great. Will you close off the question please.
Thank you for the speeding answers.