Solved

SQL Server build comma seperated lists and multiple rows

Posted on 2013-06-25
12
247 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 22

Expert Comment

by:Om Prakash
ID: 39274746
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
ID: 39274756
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
ID: 39274758
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.

 
LVL 11

Expert Comment

by:Louis01
ID: 39274760
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
ID: 39274894
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
ID: 39274898
Did you miss my post (above)?
0
 

Author Comment

by:lrbrister
ID: 39274908
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
ID: 39274937
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
ID: 39274948
Bingo,
Thanks
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39274956
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
ID: 39274969
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
ID: 39275094
No problem. Glad we all could help.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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…

730 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