May 7, 2006 at 11:27 pm
I the following query that produces a 1 column result set like so,
DECLARE @start datetime, @end datetime
SET @start = {ts '2006-04-07 00:00:00'}
SET @end = {ts '2006-05-12 00:00:00'}
SELECT (CAST(( CASE WHEN day1Hours <> 0 THEN DATEADD(day,-6, dbo.fnDatePerd(A.PerdDate)) END) AS varchar(20)) + ',' +
CAST(( CASE WHEN day2Hours <> 0 THEN DATEADD(day,-5, dbo.fnDatePerd(A.PerdDate)) END) AS varchar(20)) + ',')) as X
FROM TableA A, TableB B
WHERE 1 = A.xxid
AND a.ccid = B.ccid
AND A.DatePerd IN (SELECT DatePerd
FROM T_DatePerd
WHERE DatePerd BETWEEN @start AND @end)
ABC,DEF, HIJ
AAA, BBB, CCC
ZZZ, YYY, XXX
What I wish to do is conactenate the result set to produce like so,
ABC,DEF,HIJ,AAA,BBB,CCC,ZZZ,YYY,XXX.
Is it possible to do this without createing a Temporary Table?
May 8, 2006 at 2:20 am
declare @data varchar(1000)
select @data = coalesce(@data + ',', '') + (CAST(( CASE WHEN day1Hours 0 THEN DATEADD(day,-6, dbo.fnDatePerd(A.PerdDate)) END) AS varchar(20)) + ',' +
CAST(( CASE WHEN day2Hours 0 THEN DATEADD(day,-5, dbo.fnDatePerd(A.PerdDate)) END) AS varchar(20)) + ',')) as X
FROM TableA A, TableB B
WHERE 1 = A.xxid
AND a.ccid = B.ccid
AND A.DatePerd IN (SELECT DatePerd
FROM T_DatePerd
WHERE DatePerd BETWEEN @start AND @end)
select @data
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply