Table problem

Question

Post reply

Table problem

yapafly

SSC Veteran

Points: 292
More actions
June 4, 2008 at 10:02 pm

#188511

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..sad
here is my sql code:
select Customer_ID, Item_in_cart
from Customer_cart

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

I believe you could do what you are looking for with a matrix.

http://msdn.microsoft.com/en-us/library/ms157334.aspx

To help us help you read this[/url]For better help with performance problems please read this[/url]

yapafly SSC Veteran Points: 292 More actions · Answer 2

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;

Mike Hayes-368824 Old Hand Points: 353 More actions · Answer 3

I'm finding documentation on this functionality in Oracle, but not SQL Server. Is this supported in SQL Server?

Mohammad Mazharuddin Ehsan SSCarpal Tunnel Points: 4552 More actions · Answer 4

You can achieve the same result as follows in SQL Server:

CREATE TABLE [dbo].[Customer_cart](

[Customer_ID] [varchar](50) NOT NULL,

[Item_in_cart] [numeric](18, 0) NOT NULL

)

INSERT INTO [Customer_cart] VALUES ('12ab',123)

INSERT INTO [Customer_cart] VALUES ('12ab',122)

INSERT INTO [Customer_cart] VALUES ('13ac ',7)

SELECT Customer_ID,

(CASE (ISNULL(CAST([123] AS VARCHAR),',')) WHEN ',' THEN '' ELSE 'bag_123'+', ' END

+

CASE (ISNULL(CAST([122] AS VARCHAR),',')) WHEN ',' THEN '' ELSE 'bag_122'+', ' END

+

CASE (ISNULL(CAST([7] AS VARCHAR),',')) WHEN ',' THEN '' ELSE 'bag_007'+', ' END

) Items_in_cart

FROM

(SELECT Customer_ID, Item_in_cart FROM dbo.Customer_cart) A

PIVOT (sum(Item_in_cart)

FOR Item_in_cart IN ([123],[122],[7])) AS PVT

Result:

Customer_ID Items_in_cart

12ab bag_123, bag_122,

13ac bag_007,

Note: I have kept the field [Item_in_cart] as numeric because the pivot will not work with char.

If you must have the field [Item_in_cart] as char then you can modify the above scheme a little to add one table as the items master and have a numeric id for each item.

-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]