December 23, 2009 at 11:59 pm
I need to build a comma separated string of the multiple rows returned out of query result. Select is only on one column. Below is the query I wrote for the same
declare @l_store_list varchar(50)
set @l_store_list = '1,2'
select
store_number
from
store
where
charindex(',' + ltrim(str(store_number)) + ',', ',' + ltrim(@l_store_list) + ',') > 0
Its Working fine. But the Problem is If i changed my input as
set @l_store_list = '1, 2' (space next to comma)
Then it will return the first row instead of all records. Can anyone help on this?
Regards,
Ram
December 24, 2009 at 4:02 am
Hi,
Instead of doing the separation, try with the dynamical execution.
create table #temp
(
slno int,
NAMES varchar(10)
)
insert into #temp
select 1,'A'
union all
select 2,'AA'
union all
select 3,'AAA'
union all
select 4,'AAAA'
declare @sql nvarchar(1000),
@l_store_list varchar(50)
set @l_store_list = '1, 2'
set @sql = 'select * from #temp where slno in ('+@l_store_list+')'
exec sp_executesql @sql
December 24, 2009 at 4:26 am
Hi,
Thanks for your reply. Your qury is working fine. But I've used to the same query with replace function.
declare @l_store_list varchar(50)
set @l_store_list = '1,2'
select
store_number
from
store
where
charindex(',' + ltrim(str(store_number)) + ',', ',' + replace(@l_store_list,' ','') + ',') > 0
Can u suggest me is it the right?
December 24, 2009 at 4:29 am
I recently replaced a Cursor that built a csv string with something along the lines of this:
DECLARE @CSVString VarChar(MAX)
CREATE TABLE #Test(ID int IDENTITY, TestText VarChar(MAX))
INSERT INTO #Test(TestText)
SELECT '1'
UNION
SELECT '2'
UNION
SELECT '3'
UNION
SELECT '4'
UNION
SELECT '5'
UNION
SELECT '6'
UNION
SELECT '7'
UNION
SELECT '8'
UNION
SELECT '9'
SELECT * FROM #Test
UPDATE #Test
SET @CSVString = ISNULL(@CSVString,'') + t.TestText + ','
FROM #Test t
SELECT @CSVString
DROP TABLE #Test
December 24, 2009 at 8:16 am
When you get to the point that concatenation using a variable is insufficient, take a look at this technique.
---------------------------------------------------------------------------------------------
-- more sophisticated example, using a WHERE clause in the subquery and GROUP BY in the main
-- query to do concatenation for all combinations in the input table
---------------------------------------------------------------------------------------------
declare @data table (empname varchar(30), svcdate varchar(10), wrkGroup int)
insert into @data
Select 'Henry' as empname, '10/20/2000' as svcDate,2 as wrkGroup union all
Select 'Henry', '10/20/2000',3 union all
Select 'Henry', '10/20/2000',5 union all
Select 'Steve','08/17/2005',8 union all
Select 'Steve','08/17/2005',10 union all
Select 'Steve','08/17/2005',9 union all
Select 'Steve','08/17/2005',4 union all
Select 'Steve','08/19/2005',2 union all
Select 'Laura','09/12/2007',3
select * from @data
select empname,svcDate,stuff((SELECT ',' + cast(wrkGroup as varchar(5))
FROM @data d2
WHERE d2.empName = d1.empname and d2.svcDate = d1.svcDate -- must match GROUP BY below
ORDER BY wrkGroup
FOR XML PATH('')
),1,1,'') as [Concatenated]
from @data d1
GROUP BY empName,svcDate -- without GROUP BY multiple rows are returned
order by empName,svcDate
Merry Christmas to all. Peace on earth and goodwill towards all people.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply