• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

SQL Server build comma seperated lists and multiple rows

I have a select that pulls id's and builds a comma separated list

This
123
124
125

Turns into this
123,124,125

What I need to do is build this in groups of 7 and start a new row once 7 are reached

So..
Selecting from a list of 16
I would get

data
123,124,125,126,127,128,129
130,131,132,133,134,135,136
137,138

This code currently builds 7 manually

DECLARE @phoneNumbers varchar(max)

SELECT	@phoneNumbers = COALESCE(@phoneNumbers + ',', '') + CAST(T.PhoneNumber AS VARCHAR(10))
FROM 
		(
			SELECT TOP 7
					REPLACE(P.DID,'-','') PhoneNumber
			FROM	[dbo].[Phone Directory] P
			INNER JOIN 
					(
						SELECT	[Staffing Manager] username
						FROM	[dbo].[Staffing Manager] 
						WHERE	[status] = 'active'
					) T
				ON	P.[USER NAME] = T.[username]
			WHERE	ISNULL(REPLACE(P.DID,'-',''),0) > ISNULL(@startNumber,5613140000)-1
				AND P.[USER NAME] IS NOT NULL		
			ORDER BY [PhoneNumber]
		) T
	
SELECT @phoneNumbers phoneList

Open in new window

0
lrbrister
Asked:
lrbrister
  • 5
  • 3
  • 2
  • +2
1 Solution
 
Om PrakashCommented:
One of the option is to loop through the data and call a function which converts only 7 rows to one column

http://geekswithblogs.net/mnf/archive/2007/10/02/t-sql-user-defined-function-to-concatenate-column-to-csv-string.aspx
0
 
dsackerContract ERP Admin/ConsultantCommented:
I created a @TempTable and populated it with a series of numbers, to test this.

If you want to have a little fun with XML PATH and CTE, this query needs to be run in text mode (not grid mode) or via SQLCMD, but it demonstrates some neat tricks with SQL:
SET NOCOUNT ON

DECLARE @TempTable TABLE (Nbr varchar(3) NOT NULL)

DECLARE @Nbr varchar(3)
SET @Nbr = '115'

WHILE @Nbr < '178'
BEGIN
    INSERT INTO @TempTable VALUES (@Nbr)
    SET @Nbr = CONVERT(varchar(3), CONVERT(smallint, @Nbr) + 1)
END;

WITH myCTE (RowNbr, Nbr) AS (
SELECT  ROW_NUMBER () OVER (PARTITION BY NULL ORDER BY Nbr) AS RowNbr,
        Nbr
FROM    @TempTable)
SELECT  CASE
            WHEN RowNbr = 1 THEN ''
            WHEN (RowNbr - 1) % 7 = 0 THEN CHAR(10)
            ELSE ','
        END + Nbr
FROM myCTE
FOR XML PATH('')

SET NOCOUNT OFF

Open in new window

0
 
lrbristerAuthor Commented:
om_prakash_p

I don't expect you to do my work for sure...

But any working example code?  I don't prefer links
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
Louis01Commented:
I would approach it as follows:

Use the ROW_NUMBER to assign each phone number a sequential number:
ROW_NUMBER() OVER (ORDER BY [PhoneNumber])

Use the modulus to check if row number () returned above) division by 7 returns 0:
SELECT ROW_NUMBER() OVER (ORDER BY [PhoneNumber]) % 7

Then:
GROUP BY ROW_NUMBER() OVER (ORDER BY [PhoneNumber]) % 7
0
 
lrbristerAuthor Commented:
Louis1,
 Looks like the right direction.
I need an example with maybe using this is the base for getting everything?

I'm not getting your modulus comment

select did as phoneNumber from PhoneDirectory
0
 
dsackerContract ERP Admin/ConsultantCommented:
Did you miss my post (above)?
0
 
lrbristerAuthor Commented:
dsacker

Your post returns it but how do I handle as true rows in other apps like a GridView?

It goes in as a single datarow with line returns.

I need actual data rows
0
 
dsackerContract ERP Admin/ConsultantCommented:
Ah, okay. Probably will have to cursor it then.
/* ------------------------------------ */
/*  Whup up a test table.               */
/* ------------------------------------ */

DECLARE @TempList TABLE (Nbr varchar(3) NOT NULL)

DECLARE @Nbr        varchar(3)
SET @Nbr     = '115'

WHILE @Nbr < '178'
BEGIN
    INSERT INTO @TempList VALUES (@Nbr)
    SET @Nbr = CONVERT(varchar(3), CONVERT(smallint, @Nbr) + 1)
END

/* ------------------------------------ */
/*  Cursor it into segments of seven.   */
/* ------------------------------------ */

DECLARE @Count      smallint,
        @NbrData    varchar(80)

SET @Count = 0
SET @NbrData = ''

DECLARE @TempResults TABLE (NbrData varchar(80) NOT NULL)

DECLARE csr CURSOR LOCAL FAST_FORWARD FOR
SELECT  Nbr FROM @TempList
ORDER BY Nbr

OPEN csr

WHILE 1 = 1
BEGIN
    FETCH FROM csr INTO @Nbr
    IF @@FETCH_STATUS <> 0 BREAK

    SET @Count = @Count + 1
    IF @NbrData <> '' SET @NbrData = @NbrData + ','
    SET @NbrData = @NbrData + @Nbr

    IF @Count % 7 = 0
    BEGIN
        INSERT INTO @TempResults
        VALUES (@NbrData)

        SET @NbrData = ''
    END
END

CLOSE csr
DEALLOCATE csr

IF @NbrData <> ''
    INSERT INTO @TempResults
    VALUES (@NbrData)

SELECT * FROM @TempResults

Open in new window

0
 
lrbristerAuthor Commented:
Bingo,
Thanks
0
 
ZberteocCommented:
Well what the heck, I already built the solution anyway so here it is:

-- create a temp table to store your numbers
if object_id('tempdb..#vals') is not null	
	drop table #vals
create table #vals(id int identity, val varchar(50))
insert into #vals
select 
	cast(v as varchar) as val
from
(
select 
123 as v
union all
select 
124
union all
select 
125
union all
select 
126
union all
select 
127
union all
select 
128
union all
select 
129
union all
select 
130
union all
select 
131
union all
select 
132
union all
select 
133
union all
select 
134
union all
select 
135union all
select 
136
union all
select 
137
union all
select 
138
) q
-- create a temp table to store the list of 7 numbers
if object_id('tempdb..#vals_7') is not null	
	drop table #vals_7
create table #vals_7(id int identity, val_7 varchar(1000))
declare 
	@list varchar(1000),
	@cnt int
select 
	@list='',
	@cnt=7

while exists(select * from #vals)
begin
	-- buld the list of 7 numbers at a time
	select 
		@list=@list+val+','
	from
		#vals
	where
		id<=@cnt
	order by 
		id
	-- insert the list of 7 numbers into temp table after removing the trailing comma
	insert into #vals_7 (val_7)
	select replace(@list+'@#$',',@#$','')
	delete from #vals where id<=@cnt
	-- reinitialize variables
	select 
		@list='',
		@cnt=@cnt+7
end
--return the results
select 
	val_7 
from 
	#vals_7
order by
	id

Open in new window

0
 
lrbristerAuthor Commented:
Zbertoc,
 Sorry buddy.
Thanks for the follow-up


I tested and while both solutions worked, yours actually came out better on the execution plan.
0
 
ZberteocCommented:
No problem. Glad we all could help.
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 5
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now