Solved

SQL Server build comma seperated lists and multiple rows

Posted on 2013-06-25
12
238 Views
Last Modified: 2013-06-25
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
Comment
Question by:lrbrister
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 22

Expert Comment

by:Om Prakash
Comment Utility
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
 
LVL 20

Expert Comment

by:dsacker
Comment Utility
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
 

Author Comment

by:lrbrister
Comment Utility
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
 
LVL 11

Expert Comment

by:Louis01
Comment Utility
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
 

Author Comment

by:lrbrister
Comment Utility
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
 
LVL 20

Expert Comment

by:dsacker
Comment Utility
Did you miss my post (above)?
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:lrbrister
Comment Utility
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
 
LVL 20

Accepted Solution

by:
dsacker earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:lrbrister
Comment Utility
Bingo,
Thanks
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
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
 

Author Comment

by:lrbrister
Comment Utility
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
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
No problem. Glad we all could help.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now