Group String Concatenation

[Steve101]

I have a table with the following fields:

Date City Category Value

01-Jan-07 London A 100
02-Jan-07 London A 150
03-Jan-07 London B 120
04-Jan-07 London A 100
05-Jan-07 London B 110
06-Jan-07 London B 105
07-Jan-07 London A 100

01-Jan-07 Tokyo B 100
02-Jan-07 Tokyo C 150
03-Jan-07 Tokyo B 120
04-Jan-07 Tokyo B 100
05-Jan-07 Tokyo B 110
06-Jan-07 Tokyo C 105
07-Jan-07 Tokyo C 100

I wish to aggregate this information by City and Category, to produce the following result:

City Categories Totals

London AB 805
Tokyo BC 785

Is there a simple way that I can concatenate the Categories associated with each city, to produce the desired "Categories" field aggregation.

I know how to Crosstab to get the categories into separate columns for each City and Category, but dont know how to collapse the records further from here (from within the SQL Statement).

Any help would be appreciated,

Thanks,


Steve Lewy
Solutions Developer
SimplyData
simplydata.com.au
(dont cut corners or you'll go round in circles)

 

[mrdenny]

You'll need to write a function to handle this. There are several examples in the forums to cover this.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Catadmin]

You can also check the FAQ section, T-SQL, for a couple of FAQs on how to concatenate the categories.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Steve101]

Thanks guys,

Did a search through the forums, but didnt find anything that I could use:

(a) I have a solution working which works using the CASE statement to crosstab in an SQL serverside function, but this only works where the crosstab values are finite and known in advance; which is not the case.

(b) I have only been able to locate a single function in the T_SQL forums, and this is written in VBA. I need something that runs serverside.

Any elaboration on your references above would be helpful,

Thanks,



Steve Lewy
Solutions Developer
SimplyData
simplydata.com.au
(dont cut corners or you'll go round in circles)

 

[r937]

go back to the FAQ and look for the section called T-SQL Hints and Tips, and look for these --[ul]
[li]Concatenating a one-to-many relationship into a single column[/li]
[li]Concatenating Row Values into 1 Record/String[/li]
[li]Concatenating Row Values into 1 Record/String (revisited)[/li]
[/ul]

r937.com | rudy.ca

 

[triggerhappy]

Here is a quickie which should work for this scenario...

One thing tho...the total for each grouping is 785 and not 805

---------------------------------

SELECT * FROM
(
SELECT
DISTINCT
A.CITY
, (A.CATEGORY + B.CATEGORY) AS 'CATEGORY'
, (A.AVALUE + B.BVALUE) AS 'VALUE'
FROM
(
SELECT
CITY
, CATEGORY
, SUM(VALUE) AS 'AVALUE'
FROM
TEST
GROUP BY
CITY
, CATEGORY
) A
LEFT JOIN
(
SELECT
CITY
, CATEGORY
, SUM(VALUE) AS 'BVALUE'
FROM
TEST
GROUP BY
CITY
, CATEGORY
) B
ON
A.CITY = B.CITY
WHERE
A.CITY = B.CITY
AND
A.CATEGORY <> B.CATEGORY
) C
WHERE
SUBSTRING(CATEGORY, 1,1) < SUBSTRING(CATEGORY,2,1)

-----------------------------------

Hope this helps...
TriggerHappy

 

[Steve101]

Thanks Rudy, Triggerhappy,

It all helps, but not quite what I was looking for. I have got quite close using dynamically generated CrossTabbing (using case) and a simple SQL function, and for my requirement, this will probably suffice.

I may have oversimplified the spec. The number of categories is finite, but can be up to 10, and a city can have any number of categories.

Many thanks for all of the input,





Steve Lewy
Solutions Developer
SimplyData
simplydata.com.au
(dont cut corners or you'll go round in circles)