Connie Jerdet-Skehan
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.
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.
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.
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 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?
Also, what table name and column names do you want for the new table?
ASKER
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.
Column names **** Folders, New Casefiles *** and whatever else maybe needed.
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.
result will look like this:
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
Please see the full results at: http://rextester.com/ZEOZRS23183result 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 |
+-----+--------+--------------+
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
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 |
+-----+--------+------+-------+-------+------+--------+
ASKER
@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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much this is perfect.
Great. Will you close off the question please.
ASKER
Thank you for the speeding answers.