Append symbol in Select -Crosstab

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

SQL Server Development (2000)

Append symbol in Select -Crosstab

Author

Topic

blinton25
Yak Posting Veteran

90 Posts

Posted - 2004-03-26 : 18:33:48

Hello,

How are you today?

I am converting the following crosstab query from Access to T-SQL:


TRANSFORM "~" 
& Count(IIf(DatePart('yyyy',[F_ARRV_DAT],1,0)=
forms!DateRangePreviousCurrentYear!PrevYear,
[Final_Clean Tourists Query].[TRAV-KEY])) 
& "~" & Count(IIf(DatePart('yyyy',[F_ARRV_DAT],1,0)=
forms!DateRangePreviousCurrentYear!CurrYear,
[Final_Clean Tourists Query].[TRAV-KEY])) 
AS Expr1
SELECT [Final_Clean Tourists Query].REGIONS3,
[Final_Clean Tourists Query].regionorder
FROM [Final_Clean Tourists Query]
WHERE [Final_Clean Tourists Query].REGIONS3<>''
GROUP BY [Final_Clean Tourists Query].regionorder, 
[Final_Clean Tourists Query].REGIONS3
PIVOT Format([F_ARRV_DAT],"mmmm") in 
("January", "February", "March",
"April","May","June",
"July","August","September",
"October","November","December");

Note that in the transform I use the ~ to produce output which looks like this:


REGIONS3	regionorder	Jan
USA	             1	         20~50~
CANADA	             2	         30~15~
UK	             3           101~20~

To achieve the same crosstab in T-SQL I use the following:



SELECT [Final_Clean_Tourists_QueryView].REGIONS3,
Final_Clean_Tourists_QueryView].regionorder,
sum(
CASE (DatePart(yyyy,[F_ARRV_DAT]))
WHEN '2003' THEN (1 *(1-ABS(SIGN(DatePart(mm,F_ARRV_DAT)-1))))
else 0
END
)
 AS Jan,

sum(
CASE (DatePart(yyyy,[F_ARRV_DAT]))
WHEN '2002' THEN (1 *(1-ABS(SIGN(DatePart(mm,F_ARRV_DAT)-1))))
else 0
END
)
 AS Jan


FROM [Final_Clean_Tourists_QueryView]
WHERE [Final_Clean_Tourists_QueryView].REGIONS3<>''
GROUP BY [Final_Clean_Tourists_QueryView].regionorder,
Final_Clean_Tourists_QueryView].REGIONS3

but I am not sure if I can use an append in a Select statement to produce the same output as my first query.

Your thoughts?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-26 : 20:20:50

You can but you need to convert the values to a varchar() datatype and then use the + operator, which is the equivalent of & in T-SQL when it comes to strings.

Did you try?

- Jeff

blinton25
Yak Posting Veteran

90 Posts

Posted - 2004-03-28 : 06:38:40

Hello,

That worked great, thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources