Author |
Topic |
amurark
Yak Posting Veteran
55 Posts |
Posted - 2012-12-26 : 10:05:15
|
Hi All,Pls tell me how to get the output from attached sheet as belowLike there are below records lAstId bCreatedOnTheFly dtLastModif AcctCode AssetTag BarCode BizValDesc dAccept dAcquisition dDeprRecalc dDispos dEndAcqu dEndCnx Description dInstall dIntPay DisposProfit 2364434 0 41:02.5 NULL SR-062009-00032966 NULL NULL 00:00.0 00:00.0 00:00.0 NULL NULL NULL Motorola FR6000(HH-052009-00038337) 00:00.0 NULL NULL 2364437 0 41:02.9 NULL SR-062009-00032962 NULL NULL 00:00.0 00:00.0 00:00.0 NULL NULL NULL Motorola FR6000(HH-052009-00038338) 00:00.0 NULL NULL 2364440 1/0/1900 12:00:00 AM 6/17/2012 11:41:00 PM NULL SR-072009-00020572 NULL NULL 00:00.0 00:00.0 00:00.0 NULL NULL NULL Motorola FR6000(HH-052009-00038339) 00:00.0 NULL NULL 2364443 01/00/00 12:00:00 AM 6/17/2012 11:41:03 PM NULL SR-072009-00020571 NULL NULL 00:00.0 00:00.0 00:00.0 NULL NULL NULL Motorola FR6000(HH-052009-00038340) 00:00.0 NULL NULL 2364462 01/00/00 12:00:00 AM 11/16/2010 11:32:38 AM NULL SR-072009-00020585 NULL NULL 00:00.0 00:00.0 00:00.0 NULL NULL NULL Motorola LS1203(HH-092009-00038341) 00:00.0 NULL NULL 2364465 01/00/00 12:00:00 AM 11/16/2010 11:32:44 AM NULL HH-092009-00038342 NULL NULL 00:00.0 00:00.0 00:00.0 NULL NULL NULL Motorola LS1203(HH-092009-00038342) 00:00.0 NULL NULL ######################################################################There is another tablelNonAssetTagId dtLastModif Vendor_Code Vendor_Name Vendor_Invoice_No Type Invoice_Date Purchase_Order_No_ Division Delivery_Address Serial_No Material_Code Uninor_Part_Code Material_Description Basic_Value lamLocationId lamEmplDeptId Rate Quantity Hardware_Software AssetTag Remarks0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 0 0 NULL NULL NULL NULL NULL4251063 50:13.8 NULL Wipro Ltd 1261106140 NULL 00:00.0 1.34124E+14 IT Wipro Ltd., GNDC MYI84503AV, MYI01800H8 435508-B21 43220000-00841 HP NC364T PCI Express Quad Port 54000 0 0 27000 2 Hardware SR-062009-00032966 & SR-062009-00032962 NULL4251064 05:42.7 NA Wipro Ltd 1261106140 NULL 00:00.0 1.34124E+14 IT Wipro Ltd MYI13900WO, MYI1845003S, MYI13000BS,MYI850075V, MYI02300NA, MYI82616FV, MY18380F8X 435508-B21 43220000-00842 HP NC364T PCI Express Quad Port 182000 0 0 26000 7 Hardware SR-072009-00020572,SR-072009-00020571,SR-062009-00020685,SR-072009-00001592,SR-072009-00001376,SR-062009-00020683,SR-092009-00001617 Serial No. .MYI13900WO, MYI1845003S, MYI13000BS, MYI850075V, MYI02300NA, MYI82616FV, MY18380F8X Asset Tag .SR-072009-00020572, SR-072009-00020571, SR-062009-00020685, SR-072009-00001592, SR-072009-00001376, SR-062009-00020683, SR-092009-00001617 4251062 43:18.4 NA Wipro Ltd 1261106140 NULL 00:00.0 1.34124E+14 IT Wipro Ltd MYI14403BM 593722-B21 43220000-00840 HP NC365T - network adapter - 4 27000 0 0 27000 1 Hardware SR-062009-00033653 NULL1898213 27:53.6 NULL Wipro 9,009,204,070 NULL 00:00.0 125,094,000,000,027 NULL West Bengal NULL NK667AV ACJ 9009204070NK667AV ACJ102 India - English Localization 0 0 0 NULL 102 NULL NULL NULL1898214 27:53.7 NULL Wipro 9,009,204,070 NULL 00:00.0 125,094,000,000,027 NULL West Bengal NULL FV931AV 9009204070FV931AV102 Broadcom 802.11b/g Card 0 0 0 NULL 102 NULL NULL NULL1898215 27:53.8 NULL Wipro 9,009,204,070 NULL 00:00.0 125,094,000,000,027 NULL West Bengal NULL FV931AV ACJ 9009204070FV931AV ACJ102 India - English Localization 0 0 0 NULL 102 NULL NULL NULL1898216 27:53.9 NULL Wipro 9,009,204,070 NULL 00:00.0 125,094,000,000,027 NULL West Bengal NULL NZ400AV 9009204070NZ400AV102 ID BLK 14.0 441Xs Module 0 0 0 NULL 102 NULL NULL NULL1898217 27:54.0 NULL Wipro 9,009,204,070 NULL 00:00.0 125,094,000,000,027 NULL West Bengal NULL FV921AV 9009204070FV921AV102 160G 5400RPM FX Hard Drive 0 0 0 NULL 102 NULL NULL NULL1898218 27:54.1 NULL Wipro 9,009,204,070 NULL 00:00.0 125,094,000,000,027 NULL West Bengal NULL FV934AV 9009204070FV934AV102 DVD-RW LS FX Drive 0 0 0 NULL 102 NULL NULL NULL1898219 27:54.2 NULL Wipro 9,009,204,070 NULL 00:00.0 125,094,000,000,027 NULL West Bengal NULL FV919AV 9009204070FV919AV102 6C 47Whr 441xs Battery 0 0 0 NULL 102 NULL NULL NULL1898220 27:54.3 NULL Wipro 9,009,204,070 NULL 00:00.0 125,094,000,000,027 NULL West Bengal NULL NL827AV 9009204070NL827AV102 HP 3/3/3 WARRANTY 0 0 0 NULL 102 NULL NULL NULL1898221 27:54.4 NULL Wipro 9,009,204,070 NULL 00:00.0 125,094,000,000,027 NULL West Bengal NULL NL827AV UUF 9009204070NL827AV UUF102 Asia Pacific-English Localizatio 0 0 0 NULL 102 NULL NULL NULL1898252 27:55.0 NULL Wipro 9,009,204,070 NULL 00:00.0 125,094,000,000,027 NULL West Bengal NULL KM334AV 9009204070KM334AV102 EStar Label 0 0 0 NULL 102 NULL NULL NULL1898253 27:55.1 NULL Wipro 9,009,204,070 NULL 00:00.0 125,094,000,000,027 NULL West Bengal NULL RQ047AV 9009204070RQ047AV102 MS Vista Premium Logo Label 0 0 0 NULL 102 NULL NULL NULL1898254 27:55.2 NULL Wipro 9,009,204,070 NULL 00:00.0 125,094,000,000,027 NULL West Bengal NULL NL529AV 9009204070NL529AV102 56K MDC 1.5 modem 0 0 0 NULL 102 NULL NULL NULL1898255 27:55.3 NULL Wipro 9,009,204,070 NULL 00:00.0 125,094,000,000,027 NULL West Bengal NULL NX987AV 9009204070NX987AV102 Bluetooth 2.1+ Module 0 0 0 NULL 102 NULL NULL NULL1898256 27:55.4 NULL Wipro 9,009,204,070 NULL 00:00.0 125,094,000,000,027 NULL West Bengal NULL FV937AV 9009204070FV937AV102 14.0 HD 441xs Display 0 0 0 NULL 102 NULL NULL NULL1898257 27:55.5 NULL Wipro 9,009,204,070 NULL 00:00.0 125,094,000,000,027 NULL West Bengal NULL NK667AV 9009204070NK667AV102 Touchpad 14.0 441xs Keyboard 0 0 0 NULL 102 NULL NULL NULL1898258 27:55.6 NULL Wipro 9,009,204,070 NULL 00:00.0 125,094,000,000,027 NULL West Bengal NULL FZ959AV ACJ 9009204070FZ959AV ACJ102 India - English localization 0 0 0 NULL 102 NULL NULL NULL1898259 27:55.7 NULL Wipro 9,009,204,070 NULL 00:00.0 125,094,000,000,027 NULL West Bengal NULL VK140AV 9009204070VK140AV102 2G 333DDR3 2DM Memory Module 0 0 0 NULL 102 NULL NULL NULL1898260 27:55.8 NULL Wipro 9,009,204,070 NULL 00:00.0 125,094,000,000,027 NULL West Bengal NULL RH304AA 9009204070RH304AA102 HP USB Optical Travel Mouse 0 0 0 NULL 102 NULL NULL NULL1898261 27:55.9 NULL Wipro 9,009,204,070 NULL 00:00.0 125,094,000,000,027 NULL West Bengal NULL KX515AV 9009204070KX515AV102 Intel Core2 Duo Label 0 0 0 NULL 102 NULL NULL NULL1898262 27:56.0 NULL Wipro 9,009,204,070 NULL 00:00.0 125,094,000,000,027 NULL West Bengal NULL FZ959AV 9009204070FZ959AV102 OSDVD Vista Business 32 2009 Med 0 0 0 NULL 102 NULL NULL NULL1898263 27:56.1 NULL Wipro 9,009,204,070 NULL 00:00.0 125,094,000,000,027 NULL West Bengal NULL VD752AV 9009204070VD752AV102 Intel Core2 Duo P7570 Processor 0 0 0 NULL 102 NULL NULL NULL1898264 27:56.2 NULL Wipro 9,009,204,070 NULL 00:00.0 125,094,000,000,027 NULL West Bengal NULL EE027PA 9009204070EE027PA102 HP Koskin Entry Case 0 0 0 NULL 102 NULL NULL NULL1898265 27:56.3 NULL Wipro 9,009,204,070 NULL 00:00.0 125,094,000,000,027 NULL West Bengal NULL NQ007AV 9009204070NQ007AV102 DVD Vista Media 0 0 0 NULL 102 NULL NULL NULL1898266 27:56.4 NULL Wipro 9,009,204,070 NULL 00:00.0 125,094,000,000,027 NULL West Bengal NULL NK556AV 9009204070NK556AV102 Office Ready DVD 2009 Media 0 0 0 NULL 102 NULL NULL NULL4185183 09:09.7 NULL Wipro Ltd 1220270853 NULL 00:00.0 1.34114E+14 IT Wipro Ltd , GNDC NULL SUP-50X6CU-STRP 43220000-00767 Sup of 50x6 Cu Strips with Insul 101388 0 0 1988 50 Hardware NULL NULL4322349 33:16.5 NA Wipro Ltd 1261105601 NULL 00:00.0 1.34124E+14 IT Wipro Ltd.,SEZ, Plot 2,3 4, Knowledge Park 4, NearITPark,Gautam BudhNagar,Gnoida,GREATER NOIDA-201306,WI-Uttar Pradesh NA 540-7407 43201803-00015 HP 146GB 10000 RPM SAS Disk Driv 160000 0 0 20,000 8 Hardware SR-082009-00021307,SR-082009-00021306 NULL4322228 35:53.7 NA Wipro Ltd 1261105601 NULL 00:00.0 1.34124E+14 IT Wipro Ltd.,SEZ, Plot 2,3 4, Knowledge Park 4, NearITPark,Gautam BudhNagar,Gnoida,GREATER NOIDA-201306,WI-Uttar Pradesh NA 371-2245 43201409-00002 Optical Link card kit; IOU Devic 348000 0 0 87,000 4 Hardware SR-082009-00021307,SR-082009-00021306 NULLWhere asset tag is given in diferent format.WE need asset tag from table one match asset tag of another table were in asset tag more than two assets are present.If use inner join it checks only first record in table2 but not other records in the same field asset tag.For eg from table one should match Asset tag --> SR-062009-00032966Table 2 asset tag Asset tag -->SR-062009-00032962,SR-062009-00032963,SR-062009-00032968 SR-062009-00032969 Ankita |
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-26 : 11:44:24
|
Not Clear.Can you post sample Data with expected output with clear explanation? |
|
|
amurark
Yak Posting Veteran
55 Posts |
Posted - 2012-12-26 : 22:27:32
|
Hi Sodeep,ITs like this Table 1Invoiceno Asset Tag1 A,B,C,D2 A3 E4 D5 G,HInvoiceno Asset Tag7 A9 B10 K11 K12 M34 C56 D I want to join two tables so that i can fetch recods from both o the table , they repeate no problem in that.Suppsoe Asset tag in table2 match every record of Assettag in tbale1 and not only first record, if they get it then it shuld display.In table one somewere ASsettag is given in one line like A,B,C,dAnkita |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-26 : 23:05:02
|
do you mean this?SELECT *FROM TableA aINNER JOIN TableB bON ',' + a.AssetTag + ',' LIKE '%,' + b.AssetTag + ',%' ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
amurark
Yak Posting Veteran
55 Posts |
Posted - 2012-12-27 : 01:42:08
|
Visakh16,Thanks laot again for your expert reply......it worked for meAnkita |
|
|
|
|
|