[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 187
  • Last Modified:

SQL 2008 Record Creation

TableA:

Code  Desc
11      Car
12      Boat
13      Bike
22      House
25      Garage
46      Plane    

I need to create 4 new records for each 1 record from above table and write to a new table called tableb.
(code will be 3 char and desc  would be 50 in new table)
(the same 4 characters (1,2,3,4) will be added to the end of the current code creating new char code)
(the same 4 words (Check,Adjust,Repair,Replace) will be added to the end of the current desc creating new desc

New table should look like below:

Tableb

111   Car Check
112   Car Adjust
113   Car Repair
114   Car Replace
121   Boat Check
122   Boat Adjust
123   Boat Repair
124   Boat Replace
131   Bike Check
132   Bike Adjust
133   Bike Repair
134   Bike Replace
221   House Check
222   House Adjust
223   House Repair
224   House Replace


etc....
0
thayduck
Asked:
thayduck
  • 7
  • 4
  • 4
1 Solution
 
prequel_serverCommented:
how big is tableA? can you provide all values in Excel for me?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Not quite getting what you're asking for, but copy-paste the below T-SQL into your SSMS, execute to verify it answers your question, then modify to fit your needs
IF OBJECT_ID('tempdb..#TableA') IS NOT NULL
   DROP TABLE #TableA

IF OBJECT_ID('tempdb..#TableB') IS NOT NULL
   DROP TABLE #TableB

CREATE TABLE #TableA (code int, description varchar(50))
INSERT INTO #TableA (code, description) 
VALUES (11, 'Car'), (12, 'Boat'), (13, 'Bike'), (22, 'House'), (25, 'Garage'), (46, 'Plane')   

CREATE TABLE #TableB (code int, description varchar(50))
INSERT INTO #TableB (code, description) 
VALUES (1, 'Check'), (2, 'Adjust'), (3, 'Repair'), (4, 'Replace')

SELECT (a.code * 10) + b.code, a.description + ' ' + b.description
FROM #TableA a
   CROSS JOIN #TableB b
ORDER BY (a.code * 10) + b.code

Open in new window

0
 
prequel_serverCommented:
create a table called B:
id      descr
1      Check              
2      Adjust              
3      Repair              
4      Replace            

this should produce the results you want then insert into a new table.


SELECT A.[Code]+B.id AS ItemCode
      ,RTRIM(A.[Desc])+' '+ RTRIM(B.descr) AS ItemDescription
  FROM [A]

  CROSS JOIN [B]

  order by A.[Code],B.id

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
thayduckAuthor Commented:
0
 
prequel_serverCommented:
thanks, not needed I went ahead and provided sql code but looks like Jim beat me to it. He has a more complete solution. The answer is to use Cross Join
0
 
thayduckAuthor Commented:
CREATE TABLE #TableA (code int, description varchar(50))
INSERT INTO #TableA (code, description)
VALUES (11, 'Car'), (12, 'Boat'), (13, 'Bike'), (22, 'House'), (25, 'Garage'), (46, 'Plane')  


So, I would have to put all 75 values here ?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
> .. for each 1 record from above table
Assuming it's already a table than use that.  I just used #TableA and populated it that way so I can test it in my SSMS, as I'm not connected to your data source so I couldn't be certain that it works.  

If it's not already in a table, but only in your Excel doc, then import that into a table.
0
 
prequel_serverCommented:
note Jim's solution produces a final table having an int type for the codem hence the X 10 in:  (a.code * 10) + b.code

use: a.code + b.code

if you're working with char concatenation
0
 
thayduckAuthor Commented:
drop table #tableb

CREATE TABLE #TableB (code int, description varchar(7))
INSERT INTO #TableB (code, description)
VALUES (1, 'Check'), (2, 'Adjust'), (3, 'Repair'), (4, 'Replace')

select *   from #tableb

SELECT (a.repcd * 10) + b.code, rtrim(a.[repcd-name]) + ' ' + rtrim(b.description)
FROM dbo.repaircodenew a
   CROSS JOIN #TableB b
ORDER BY (a.repcd * 10) + b.code




This is working except for small issue that I tried to fix with  'rtrim' , but does not help.
Is there a way to get rid of all the spaces between repcd-name and b.description in most of records ?


(No column name)      (No column name)
154      Steering Replace
161      Suspension Check
162      Suspension Adjust
163      Suspension Repair
164      Suspension Replace
171      Tires/Tubes Check
172      Tires/Tubes Adjust
173      Tires/Tubes Repair
174      Tires/Tubes Replace
181      Wheels/Rims/Hubs           Check
182      Wheels/Rims/Hubs           Adjust
183      Wheels/Rims/Hubs           Repair
184      Wheels/Rims/Hubs           Replace
191      Automatic Chassis Lube Check
192      Automatic Chassis Lube Adjust
193      Automatic Chassis Lube Repair
194      Automatic Chassis Lube Replace
211      Front Axle Driven Check
212      Front Axle Driven Adjust
213      Front Axle Driven Repair
214      Front Axle Driven Replace
221      Rear Axle Driven          Check
222      Rear Axle Driven          Adjust
223      Rear Axle Driven          Repair
224      Rear Axle Driven          Replace
231      Clutch                     Check
232      Clutch                     Adjust
233      Clutch                     Repair
234      Clutch                     Replace
241      Drive Shafts              Check
242      Drive Shafts              Adjust
243      Drive Shafts              Repair
244      Drive Shafts              Replace
251      Transfer Case              Check
252      Transfer Case              Adjust
253      Transfer Case              Repair
254      Transfer Case              Replace
261      Transmission Main         Check
262      Transmission Main         Adjust
263      Transmission Main         Repair
264      Transmission Main         Replace
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>rtrim(a.[repcd-name]) + ' ' + rtrim(b.description)
Use LTRIM and RTRIM.  Last I recall there's not a TRIM that covers both.
ltrim(rtrim(a.[repcd-name])) + ' ' + ltrim(rtrim(b.description))

Open in new window

0
 
prequel_serverCommented:
for a small dimension table like this I would just clean up in excel. Here's the final version.
300-codes.xlsx
0
 
thayduckAuthor Commented:
Thanks for the Help.
0
 
prequel_serverCommented:
no assist points for me? :(
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim
0
 
prequel_serverCommented:
BTW, did the rtrim function work for you? it didn't remove the trailing spaces for me? usually it works but I think there's something odd about the data you provided.  I think it may contain Non-ascii characters that are non-visible so it looks like spaces but in reality it's not.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now