SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Concatenating data from separate rows
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

meef
Yak Posting Veteran

97 Posts

Posted - 07/23/2013 :  22:41:15  Show Profile  Reply with Quote
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_prospect
Prospect_id (PK, unique)
Prospect_batch_id(unique)
Prospect_record_num
Client_code

This 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_batch
Prospect_batch_id (PK, unique)
Prospect_batch_num


This is the table that contains the various weights and classes for the individual records in the first table (tbl_prospect):

Tbl_prospect_clwt
Prospect_clwt_id (pk, unique)
Prospect_id(unique)
Class
weight



Generally 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.

Edited by - meef on 07/23/2013 22:43:22

meef
Yak Posting Veteran

97 Posts

Posted - 07/23/2013 :  23:52:43  Show Profile  Reply with Quote
Here is my first attempt which is most likely completely wrong:



-- customer order nums
DECLARE @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
        
 END

select @li as [li], @weight as [weight]

 

 



Running that I only get a single weight for that ID, which has two weights associated with it.

Edited by - meef on 07/24/2013 00:40:47
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 07/24/2013 :  00:54:00  Show Profile  Reply with Quote
I'm not getting the exact point.. however I think you wanted to have comma separated weights per prospect_id...
refer this link: http://blog.sqlauthority.com/2012/09/14/sql-server-grouping-by-multiple-columns-to-single-column-as-a-string/
SELECT
	t.Prospect_id
	, STUFF((SELECT ',' + s.weight
				FROM Tbl_prospect_clwt s
				WHERE s.Prospect_id = t.Prospect_id
				FOR XML PATH('')
			),1,1,'') AS CSV
FROM Tbl_prospect_clwt AS t
GROUP BY t.Prospect_id
GO


--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/24/2013 :  01:38:09  Show Profile  Reply with Quote

DECLARE @YourRequiredBatchID int

SET @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 ClassWeightDetails
FROM Tbl_prospect AS p
WHERE p.Prospect_batch_id = @YourRequiredBatchID


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

meef
Yak Posting Veteran

97 Posts

Posted - 07/24/2013 :  08:46:53  Show Profile  Reply with Quote
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 ClassWeightDetails
FROM 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_id
WHERE tb.Prospect_id = @YourRequiredBatchID



This is my normal query that I need to incorporate:


select *, b.*, c.*
from tbl_prospect tb
inner join tbl_prospect_clwt b on b.prospect_id = tb.prospect_id
left join tbl_prospect_batch c on c.prospect_batch_id= tb.prospect_batch_id
where  tb.client_code like 'gfw%' 

Edited by - meef on 07/24/2013 09:27:29
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 07/25/2013 :  00:50:56  Show Profile  Reply with Quote
what all values you need in the final output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000