Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Output as required format

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 below

Like 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 table

lNonAssetTagId 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 Remarks
0 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 0 0 0 NULL NULL NULL NULL NULL
4251063 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 NULL
4251064 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 NULL
1898213 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 NULL
1898214 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 NULL
1898215 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 NULL
1898216 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 NULL
1898217 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 NULL
1898218 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 NULL
1898219 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 NULL
1898220 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 NULL
1898221 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 NULL
1898252 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 NULL
1898253 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 NULL
1898254 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 NULL
1898255 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 NULL
1898256 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 NULL
1898257 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 NULL
1898258 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 NULL
1898259 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 NULL
1898260 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 NULL
1898261 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 NULL
1898262 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 NULL
1898263 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 NULL
1898264 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 NULL
1898265 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 NULL
1898266 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 NULL
4185183 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 NULL
4322349 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 NULL
4322228 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 NULL


Where 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-00032966

Table 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?
Go to Top of Page

amurark
Yak Posting Veteran

55 Posts

Posted - 2012-12-26 : 22:27:32
Hi Sodeep,
ITs like this



Table 1


Invoiceno Asset Tag
1 A,B,C,D
2 A
3 E
4 D
5 G,H


Invoiceno Asset Tag
7 A
9 B
10 K
11 K
12 M
34 C
56 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,d









Ankita
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-26 : 23:05:02
do you mean this?

SELECT *
FROM TableA a
INNER JOIN TableB b
ON ',' + a.AssetTag + ',' LIKE '%,' + b.AssetTag + ',%'


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

amurark
Yak Posting Veteran

55 Posts

Posted - 2012-12-27 : 01:42:08
Visakh16,

Thanks laot again for your expert reply......it worked for me

Ankita
Go to Top of Page
   

- Advertisement -