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.
Author |
Topic |
meef
Posting Yak Master
113 Posts |
Posted - 2013-07-23 : 22:41:15
|
Ok, I have three tables worth of data. They basically hold data that is keyed into a system for potential clients and include stuff like names, weights, classes of weight, addresses etc. The main table that holds data on a record by record basis is set up like this:Tbl_prospectProspect_id (PK, unique)Prospect_batch_id(unique)Prospect_record_numClient_codeThis table contains the batches (or collections of the data above). Basically a collection of bills is called a batch, so if a batch has 18 bills in it, those 18 bills are keyed and assigned the prospect_batch_id above.Tbl_prospect_batchProspect_batch_id (PK, unique)Prospect_batch_numThis is the table that contains the various weights and classes for the individual records in the first table (tbl_prospect):Tbl_prospect_clwtProspect_clwt_id (pk, unique)Prospect_id(unique)ClassweightGenerally without worrying about the multiple classes and weights it's easy to just join all three tables and pull the fields I need, but now that I have to get multiple classes and weights I have no clue how to actually grab them and delimit them with a comma.I think I'm only going to be working on tbl_prospect and tbl_prospect_clwt, and if I put a filter in place for the prospect_batch_id I can see the individual record in tbl_prospect as well as the multiple classes and weights in the clwt table, as seen here:So record 18 of that batch is comprised of two shipments, one that is class 100/weight 1623 and one that is class 70/weight 438, just not sure how to actually grab both of those for each record and delimit them. Hopefully this makes sense. |
|
meef
Posting Yak Master
113 Posts |
Posted - 2013-07-23 : 23:52:43
|
Here is my first attempt which is most likely completely wrong:-- customer order numsDECLARE @weight as varchar(50)DECLARE @class as varchar(50)DECLARE @temp_text as varchar(8000)DECLARE @li as varchar (8000)DECLARE @prospect_id as varchar(50) DECLARE p_dataset CURSOR FOR SELECT prospect_id FROM tbl_prospect WHERE client_code='GFW' and prospect_id='09920817-F41A-4D57-A829-84F3D4959329' OPEN p_dataset FETCH NEXT FROM p_dataset INTO @prospect_id WHILE @@FETCH_STATUS = 0 BEGIN SET @temp_text = '' DECLARE p_dataset1 CURSOR FOR SELECT d.weight FROM tbl_prospect_clwt tb inner join tbl_prospect b on b.prospect_id = tb.prospect_id inner join tbl_prospect_clwt d on tb.prospect_id = d.prospect_id WHERE d.prospect_id = @prospect_id OPEN p_dataset1 FETCH NEXT FROM p_dataset1 INTO @weight WHILE @@FETCH_STATUS = 0 BEGIN SET @temp_text = @temp_text + @weight+ ',' -- , delimits weights FETCH NEXT FROM p_dataset1 INTO @weight END CLOSE p_dataset1 DEALLOCATE p_dataset1 IF CHARINDEX(',', @temp_text) <> 0 SET @temp_text = SUBSTRING(@temp_text, 0, LEN(@temp_text) - 1) -- remove last comma SET @temp_text = @temp_text + CHAR(9) -- delimiter after customer order numbers, needed even if there are none SET @temp_text = @temp_text + CHAR(9) -- extra delimiter because of field not in system SET @temp_text = @temp_text + @li + CHAR(9) CLOSE p_dataset DEALLOCATE p_dataset ENDselect @li as [li], @weight as [weight] Running that I only get a single weight for that ID, which has two weights associated with it. |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-24 : 01:38:09
|
[code]DECLARE @YourRequiredBatchID intSET @YourRequiredBatchID = <set your batchid value here>SELECT p.Prospect_id ,p.Client_code , STUFF((SELECT ', Class: ' + CAST(Class AS varchar(10)) + ' Weight: ' + CAST(weight AS varchar(10)) FROM Tbl_prospect_clwt WHERE Prospect_id = p.Prospect_id FOR XML PATH('') ),1,1,'') AS ClassWeightDetailsFROM Tbl_prospect AS pWHERE p.Prospect_batch_id = @YourRequiredBatchID[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
meef
Posting Yak Master
113 Posts |
Posted - 2013-07-24 : 08:46:53
|
EDIT: Ok, your example is much simpler and also does what I'm trying to do. I still have the same problem though in getting that data in with my normal query, but this version is easier to work with than temp tables so I'll keep trying.Here is the tweaked version I used:DECLARE @YourRequiredBatchID varchar(50)SET @YourRequiredBatchID = '09920817-F41A-4D57-A829-84F3D4959329'SELECT tb.Prospect_id,tb.Client_code, STUFF((SELECT ',' + CAST(Class AS varchar(10)) + ',' + CAST(weight AS varchar(10)) FROM Tbl_prospect_clwt WHERE Prospect_id = tb.Prospect_id FOR XML PATH('')),1,1,'') AS ClassWeightDetailsFROM Tbl_prospect AS tb inner join tbl_prospect b on b.prospect_id = tb.prospect_id inner join tbl_prospect_batch d on b.prospect_id = tb.prospect_idWHERE tb.Prospect_id = @YourRequiredBatchID This is my normal query that I need to incorporate:select *, b.*, c.*from tbl_prospect tbinner join tbl_prospect_clwt b on b.prospect_id = tb.prospect_idleft join tbl_prospect_batch c on c.prospect_batch_id= tb.prospect_batch_idwhere tb.client_code like 'gfw%' |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-07-25 : 00:50:56
|
what all values you need in the final output?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|