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
 Concatenating data from separate rows

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

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-07-24 : 00:54:00
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

52326 Posts

Posted - 2013-07-24 : 01:38:09
[code]
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
[/code]

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

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 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%'
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -