turn rows into comma seperated

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

turn rows into comma seperated

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-08-29 : 11:59:36

Hi,

i am trying to get all the dates of month of year. Month and and year will be input to my procedure.

here is my try



declare @month int, @year int

set @month = 8

set @year = 2013            
              
              
              

select CAST(CAST(@year AS VARCHAR) + '-' + CAST(@Month AS VARCHAR) + '-01' AS DATETIME) + Number

 FROM master..spt_values WHERE type = 'P' AND

(CAST(CAST(@year AS VARCHAR) + '-' + CAST(@Month AS VARCHAR) + '-01' AS DATETIME) + Number )
<
DATEADD(mm,1,CAST(CAST(@year AS VARCHAR) + '-' + CAST(@Month AS VARCHAR) + '-01' AS DATETIME) )

Results:


2013-08-01 00:00:00.000
2013-08-02 00:00:00.000
2013-08-03 00:00:00.000
2013-08-04 00:00:00.000
2013-08-05 00:00:00.000
2013-08-06 00:00:00.000
2013-08-07 00:00:00.000
2013-08-08 00:00:00.000
2013-08-09 00:00:00.000
2013-08-10 00:00:00.000
2013-08-11 00:00:00.000
2013-08-12 00:00:00.000
2013-08-13 00:00:00.000
2013-08-14 00:00:00.000
2013-08-15 00:00:00.000
2013-08-16 00:00:00.000
2013-08-17 00:00:00.000
2013-08-18 00:00:00.000
2013-08-19 00:00:00.000
2013-08-20 00:00:00.000
2013-08-21 00:00:00.000
2013-08-22 00:00:00.000
2013-08-23 00:00:00.000
2013-08-24 00:00:00.000
2013-08-25 00:00:00.000
2013-08-26 00:00:00.000
2013-08-27 00:00:00.000
2013-08-28 00:00:00.000
2013-08-29 00:00:00.000
2013-08-30 00:00:00.000
2013-08-31 00:00:00.000

How can i make this reslut as single row with comma seperated and also i wanted to remove the time from this result

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-08-29 : 12:50:46

Here is one way:

declare @month int, @year int
set @month = 8
set @year = 2013            

SELECT
	STUFF(DateString, 1, 1, '') AS DateString
FROM
(
	SELECT 
		','  + CONVERT(VARCHAR(10), (DATEADD(DAY, Number, DATEADD(MONTH, @Month - 1, DATEADD(YEAR, @Year - 1900, '19000101')))), 121)
	FROM 
		master..spt_values 
	WHERE 
		type = 'P' 
		AND 
			DATEADD(DAY, Number, DATEADD(MONTH, @Month - 1, DATEADD(YEAR, @Year - 1900, '19000101')))
			<
			DATEADD(MONTH, 1, (DATEADD(MONTH, @Month - 1, DATEADD(YEAR, @Year - 1900, '19000101'))))
	ORDER BY
		DATEADD(DAY, Number, DATEADD(MONTH, @Month - 1, DATEADD(YEAR, @Year - 1900, '19000101')))
	FOR XML PATH('')
) AS T (DateString)

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-29 : 12:56:09

Here is another:

[CODE]

SELECT STUFF((SELECT ', ' + (
select CAST(@year AS VARCHAR) + '-' + FORMAT(@Month, '00') + '-' + FORMAT(Number, '00'))
FROM master..spt_values WHERE type = 'P' AND
Number BETWEEN 1 and DATEDIFF(dd, DATEADD(mm, @month-1, 0), DATEADD(mm, @month, 0))
FOR XML PATH('')), 1,1,'')

[/CODE]

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-08-29 : 13:37:18

Hi Lamprey thanks for your input and it works fine

when i assign the output to variable it gives error. how to assign the comma separated output to a variable.

Hi MuMu88,

Your query give format error.

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-08-29 : 13:46:26

Comma separated working fine. how can i pass this output in variable which will be used for pivot. pivot expects the values in []

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-08-29 : 13:56:46

What pivot? I don't see any mention of a pivot in your question about string concatenation. If you need to add square brackets, just add them as part of the string concatenation.

',[' + <string> + ']'

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-08-29 : 14:21:22

Hi lamprey,

Thanks for the reply. Here is my fine query


 declare @month int, @year int
 declare @cols varchar
set @month = 8
set @year = 2013     
      


select @cols = Records.Output from 
(
SELECT
	STUFF(DateString, 1, 1, '') as Output 
FROM
(   
	SELECT 
		',[' +  + CONVERT(VARCHAR(10), (DATEADD(DAY, Number, DATEADD(MONTH, @Month - 1, DATEADD(YEAR, @Year - 1900, '19000101')))), 121) + ']'
	FROM 
		master..spt_values 
	WHERE 
		type = 'P' 
		AND 
			DATEADD(DAY, Number, DATEADD(MONTH, @Month - 1, DATEADD(YEAR, @Year - 1900, '19000101')))
			<
			DATEADD(MONTH, 1, (DATEADD(MONTH, @Month - 1, DATEADD(YEAR, @Year - 1900, '19000101'))))
	ORDER BY
		DATEADD(DAY, Number, DATEADD(MONTH, @Month - 1, DATEADD(YEAR, @Year - 1900, '19000101')))
	FOR XML PATH('')
) AS T(DateString) 
) Records


SELECT  *
FROM    ( SELECT Banner_ID, [Date_Of_Day]
                  , Records
          FROM      dbo.weekly where DATEPART(MM,  Date_Of_Day) = 8  and DATEPART(yy, Date_Of_Day) = 2013
        ) p PIVOT ( max(records)
                    FOR [Date_Of_Day] 
                      IN (@cols) AS pvt

@cols shows runtime error as"Incorrect syntax near '@cols'."

am i doing anyting wrong

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-08-29 : 14:25:38

I think you need to turn your pivot into dynamic sql in order to use a variable like that.

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-08-29 : 14:35:43

Could you please help me on the dynamic sql by tuning my query. i am new to dynamic sql

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-08-29 : 15:41:45

Finally i am done with my requirement. Thanks a lot for the help everyone.

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-08-30 : 03:13:56

declare @Date Date='2013-07-01',@StartOfWeek tinyint=1;
declare @Month tinyint= Month(@Date);
declare @Dates table(Date date,weeknumber as datepart(week,date))

SET DATEFIRST @StartOfWeek;

WHILE (MONTH(@Date) = @Month)
BEGIN
INSERT @Dates (Date) SELECT @Date;
SET @Date = DATEADD(DD,1,@Date);
END

SELECT Date
FROM @Dates ORDER BY Date;

veeranjaneyulu

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2013-08-30 : 10:07:19

hi,

How can i make the below query output to comma separated value


declare	@year	int	= 2013,
	@week 	int 	= 2  


declare	@dte	date

select	@dte = dateadd(week, @week - 1, dateadd(year, @year - 1900, 0));
BEGIN
SELECT
	STUFF(cnvDate, 1, 1, '') as Output 
FROM(
SELECT ',[' + convert(varchar, DATE, 101) + ']'  AS cnvDate
FROM
(
	SELECT	[DATE]	= DATEADD(DAY, n, dateadd(day, (datediff(day, '17530107', @dte) / 7) * 7, '17530107'))
	FROM	(
			VALUES (0), (1), (2), (3), (4), (5), (6)) num (n)
) d WHERE	[DATE]	>= dateadd(year, @year - 1900, 0)
AND	[DATE]	<= dateadd(year, @year - 1900 + 1, -1))

END

I tried little bit. seems using stuff is not working. can any one help me in this

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2013-08-31 : 08:53:14

Hi ,

Please Run The Query .

declare @year int = 2013,
@week int = 2

--Declare @Cols varchar(1000),@Query varchar(1000)
declare @dte date

select @dte = dateadd(week, @week - 1, dateadd(year, @year - 1900, 0));

select STUFF((select ','+Quotename(Output)as [text()]
from (SELECT
STUFF(cnvDate, 1, 1, '') as Output
FROM(
SELECT ',' + convert(varchar, DATE, 101) + '' AS cnvDate
FROM
(
SELECT [DATE] = DATEADD(DAY, n, dateadd(day, (datediff(day, '17530107', @dte) / 7) * 7, '17530107'))
FROM (
VALUES (0), (1), (2), (3), (4), (5), (6)) num (n)
)d WHERE[DATE] >= dateadd(year, @year - 1900, 0)
AND [DATE] <= dateadd(year, @year - 1900 + 1, -1)
)t)x
order by Output for xml path('')),1,1,'') as 'OUTPUT'

veeranjaneyulu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources