Table problem

[yapafly]

Hi, All..
I hope someone can help me...
I'm newbie in SSRS..
My Problem is:
I have generated a table like below:
Customer_ID Item_in_cart
===============================
12ab bag_123
12ab bag_122
13ac bag_007

But i hope my table that be generated can like this:
Customer_ID Item_in_cart
===============================
12ab bag_123, bag_122
13ac bag_007


so i not sure in visual studio can do it or not..:-(
here is my sql code:

select Customer_ID, Item_in_cart
from Customer_cart

i hope someone can help me...

 

[xlbo]

Do it in the SQL, not in SSRS

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[BobHartung]

If you are using SQL Server 2005, you can use Common Table Expressions to solve this problem. Try something like this...

INSERT INTO @CustomerItems
SELECT Customer_ID
,Item_in_cart
,ROW_NUMBER() OVER(PARTITION BY Customer_ID ORDER BY Item_in_cart) AS ItemRank
FROM Customer_cart;

WITH CONCATENATED_ITEMS (Customer_ID, ItemRank, Item_in_cart) AS
(
SELECT Customer_ID
,ItemRank
,CAST(Item_in_cart AS VARCHAR(MAX))
FROM @CustomerItems
WHERE ItemRank = 1

UNION ALL

SELECT ci.Customer_ID
,ci.ItemRank
,CAST(cci.Item_in_cart + ', ' + ci.Item_in_cart AS VARCHAR(MAX))
FROM @CustomerItems ci
INNER JOIN CONCATENATED_ITEMS cci ON cci.Customer_ID = ci.Customer_ID
AND cci.ItemRank + 1 = ci.ItemRank
),
HIGHEST_RANK AS
(
SELECT MAX(ItemRank) ItemRank
,Customer_ID
FROM @CustomerItems
GROUP BY Customer_ID
)
SELECT ci.Customer_ID
,ci.Item_in_cart AS ItemList
FROM CONCATENATED_ITEMS ci
INNER JOIN HIGHEST_RANK hr ON hr.Customer_ID = ci.Customer_ID
AND hr.ItemRank = ci.ItemRank

 

[BobHartung]

Oh.. and don't forget to declare the @CustomerItems table....

DECLARE @CustomerItems TABLE (Customer_ID VARCHAR(50), Item_in_cart VARCHAR(50))

 

[BobHartung]

Sorry again... the PROPER declaration is....

DECLARE @CustomerItems TABLE (Customer_ID VARCHAR(50), Item_in_cart VARCHAR(50), ItemRank INT)

man.. it's been a long day...

 

[yapafly]

Thanks All, now its work via this method.

select
deptno,
substr(SYS_CONNECT_BY_PATH(lname, ','),2) name_list
from
(
select
lname,
deptno,
count(*) OVER ( partition by deptno ) cnt,
ROW_NUMBER () OVER ( partition by deptno order by lname) seq
from
igribun.emp
where
deptno is not null)
where
seq=cnt
start with
seq=1
connect by prior
seq+1=seq
and prior
deptno=deptno;