?
Solved

SQL Server build comma seperated lists and multiple rows

Posted on 2013-06-25
12
Medium Priority
?
250 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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 2000 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 27

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 27

Expert Comment

by:Zberteoc
ID: 39275094
No problem. Glad we all could help.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

777 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