compress the details of a column

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

Transact-SQL (2005)

compress the details of a column

Author

Topic

ganny
Yak Posting Veteran

51 Posts

Posted - 2009-04-06 : 03:10:58

Hi All,

i have a same customer with different branch details. I want to show the customer details in a single row.

Example:

Customer Branch
-------------------
AAA Delhi
AAA Newyork
AAA Dubai
BBB Mumbai
BBB London
BBB France

The expected result is:

Customer Branch
-------------------
AAA Delhi, Newyork, Dubai
BBB Mumbai, London, France

Can anyone help me, how to solve the above.

Thank you.

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-04-06 : 03:20:11

select distinct Customer,stuff((select ',' + Branch from Customers where Customer = C.Customer for xml path('')),1,1,'') as Branches
from Customers C

ganny
Yak Posting Veteran

51 Posts

Posted - 2009-04-06 : 03:39:18

Its not working..

Please assist me.

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-04-06 : 04:00:19

What is meant by "its not working." ? Its not compiling or it is not giving intended results.

This is my worked script.
create table Customers
(
Customer varchar(100),
Branch varchar(100)
)
insert into Customers values ('AAA','Delhi')
insert into Customers values ('AAA','Delhi1')
insert into Customers values ('AAA','Delhi2')
insert into Customers values ('ABB','Delhi')
insert into Customers values ('ABB','Delhi4')
insert into Customers values ('ABB','Delhi5')

select distinct Customer,stuff((select ',' + Branch from Customers where Customer = C.Customer for xml path('')),1,1,'') as Branches
from Customers C

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-06 : 04:28:24

Maybe he is using SQL Server 2000 and didn't notice the forum header?

E 12°55'05.63"
N 56°04'39.26"

ganny
Yak Posting Veteran

51 Posts

Posted - 2009-04-06 : 04:31:54

yes, its not giving the expexted result i mentioned. Its showing the same details in the table after executing the query.

quote:
select distinct Customer,stuff((select ',' + Branch from Customers where Customer = C.Customer for xml path('')),1,1,'') as Branches
from Customers C

Customer Branch
-------------------
AAA Delhi
AAA Newyork
AAA Dubai
BBB Mumbai
BBB London
BBB France

Please assist to show the result as i expected.

The expected result is:

Customer Branch
-------------------
AAA Delhi, Newyork, Dubai
BBB Mumbai, London, France

Thank you.

ganny
Yak Posting Veteran

51 Posts

Posted - 2009-04-06 : 04:33:23

quote:
Originally posted by Peso

Maybe he is using SQL Server 2000 and didn't notice the forum header?

E 12°55'05.63"
N 56°04'39.26"

I am using SQL Server.2005.

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-04-06 : 04:40:59

Is the spaces are not coming? is that the problem you are facing? then use this query.

select distinct Customer,stuff((select ', ' + Branch from Customers where Customer = C.Customer for xml path('')),1,2,'') as Branches
from Customers C

if it is also not helpful, then post here what's the above query's output in our machine. and also tell me you are using sql server2000 or sql server 2005?

And as you are a starting member of forums, i am giving a advice. please give more details when you are asking questions or sending replies. the more info that you give, the more chances that you get answer.

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-04-06 : 04:41:00

ganny
Yak Posting Veteran

51 Posts

Posted - 2009-04-06 : 05:04:16

Dear Reddy,

I am using SQL 2005. Your query gives the different result than what i expexted. Please find the below result of your query executing.

Your Query Result for
select distinct Customer,stuff((select ',' + Branch from Customers where Customer = C.Customer for xml path('')),1,1,'') as Branches
from Customers C

Customer Branch
-------------------
AAA Delhi
AAA Newyork
AAA Dubai
BBB Mumbai
BBB London
BBB France

Your Query Result for
select distinct Customer,stuff((select ',' + Branch from Customers where Customer = C.Customer for xml path('')),1,2,'') as Branches
from Customers C

Customer Branch
-------------------
AAA elhi
AAA ewyork
AAA ubai
BBB umbai
BBB ondon
BBB rance

Kindly note that, i want the result as below:

Customer Branch
-------------------
AAA Delhi, Newyork, Dubai
BBB Mumbai, London, France

micnie_2020
Posting Yak Master

232 Posts

Posted - 2009-04-06 : 05:21:57

Dear Sir,

Here is the solution:-

----------Create Table & dump Data for Testing ------------
create table tabCustomers
(
Customer varchar(100),
Branch varchar(100)
)

insert into tabCustomers (Customer,Branch) values ('AAA','Delhi')
insert into tabCustomers (Customer,Branch) values ('AAA','Delhi1')
insert into tabCustomers (Customer,Branch) values ('AAA','Delhi2')
insert into tabCustomers (Customer,Branch) values ('ABB','Delhi')
insert into tabCustomers (Customer,Branch) values ('ABB','Delhi4')
insert into tabCustomers (Customer,Branch) values ('ABB','Delhi5')

------------- Create Function -----------------

CREATE FUNCTION dbo.getbranch2
(
@cus VARCHAR(32)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @r VARCHAR(8000)
SELECT @r = ISNULL(@r+',', '')
+ branch
FROM tabCustomers
WHERE customer = @cus
RETURN @r
END
GO

----- Retrieve Information to get the output required ----

SELECT customer,
dbo.getbranch2(customer)
FROM tabCustomers
group by customer

I have tested using ms sql 2000 & it's definetely will work in ms sql 2005 as well.

Thank you.

Regards,
Michelle

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-06 : 05:32:00

quote:
Originally posted by ganny

quote:
Originally posted by Peso

Maybe he is using SQL Server 2000 and didn't notice the forum header?

E 12°55'05.63"
N 56°04'39.26"

I am using SQL Server.2005.

What is the Compatibility level of the database

sp_helpdb your_db

Madhivanan

Failing to plan is Planning to fail

ganny
Yak Posting Veteran

51 Posts

Posted - 2009-04-06 : 05:33:14

Is there any solution without creating a Function since i dont have access to create functions. Please advise.

Thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-06 : 05:58:55

The "1,2" part should be "1,1".

select distinct Customer,stuff((select ',' + Branch from Customers where Customer = C.Customer for xml path('')),1,1,'') as Branches

E 12°55'05.63"
N 56°04'39.26"

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-04-06 : 06:03:53

Probably your server is 2000 only. then use the below proc.

declare @Branches varchar(100)
declare @temp table
(
Customer varchar(100),
Branch varchar(max),
Visited int
)
insert into @temp
select distinct Customer,'',0 from Customers
while( (select count(*) from @temp where Visited = 0) > 0)
begin
set @Branches = ''
SELECT @Branches = coalesce(@Branches+',', '')
+Branch
FROM Customers
WHERE customer = (select top 1 Customer from @temp where Visited = 0)
update @temp set Branch = stuff(@Branches,1,1,'') , Visited = 1
WHERE customer = (select top 1 Customer from @temp where Visited = 0)
end
select * from @temp

ganny
Yak Posting Veteran

51 Posts

Posted - 2009-04-06 : 06:12:46

yes sir, Its working fine now.

Thank you all for your kind help.

micnie_2020
Posting Yak Master

232 Posts

Posted - 2009-04-07 : 20:53:03

Dear Ganny,

This is another on doing it:-
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=123332


/*--Create & Dump data
create table tabCustomers
(
Customer varchar(100),
Branch varchar(100)
)

insert into tabCustomers (Customer,Branch) values ('AAA','Delhi')
insert into tabCustomers (Customer,Branch) values ('AAA','Delhi1')
insert into tabCustomers (Customer,Branch) values ('AAA','Delhi2')
insert into tabCustomers (Customer,Branch) values ('ABB','Delhi')
insert into tabCustomers (Customer,Branch) values ('ABB','Delhi4')
insert into tabCustomers (Customer,Branch) values ('ABB','Delhi5')
insert into tabCustomers (Customer,Branch) values ('AAC','Delhi9')
insert into tabCustomers (Customer,Branch) values ('AAD','Delhi10')
insert into tabCustomers (Customer,Branch) values ('AAD','Delhi11')
insert into tabCustomers (Customer,Branch) values ('ACB','Delhi6')
insert into tabCustomers (Customer,Branch) values ('ACB','Delhi7')
insert into tabCustomers (Customer,Branch) values ('ACB','Delhi8')
--*/

DECLARE @customer varchar(20)
DECLARE merge_cursor CURSOR FAST_FORWARD FOR 
SELECT distinct customer FROM tabCustomers
DECLARE @r VARCHAR(8000) 


OPEN merge_cursor 
declare @temp table
(
Customer varchar(100),
Branch varchar(200)
)

FETCH NEXT FROM merge_cursor INTO @customer 

WHILE @@FETCH_STATUS = 0
BEGIN
set @r=''
SELECT @customer=customer,
@r = ISNULL(@r+',', '') 
        + branch 

        FROM tabCustomers 
        WHERE customer = @customer 

insert into @temp
SELECT @customer,@r 


FETCH NEXT FROM merge_cursor INTO @customer
END

select customer,branch=right(branch,(len(branch)-1))  from @temp

CLOSE merge_cursor 
DEALLOCATE merge_cursor

Thank you.

Regards,
Michelle

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-08 : 03:23:29

quote:

Originally posted by micnie_2020

Dear Ganny,

This is another on doing it:-
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=123332


/*--Create & Dump data
create table tabCustomers
(
Customer varchar(100),
Branch varchar(100)
)

insert into tabCustomers (Customer,Branch) values ('AAA','Delhi')
insert into tabCustomers (Customer,Branch) values ('AAA','Delhi1')
insert into tabCustomers (Customer,Branch) values ('AAA','Delhi2')
insert into tabCustomers (Customer,Branch) values ('ABB','Delhi')
insert into tabCustomers (Customer,Branch) values ('ABB','Delhi4')
insert into tabCustomers (Customer,Branch) values ('ABB','Delhi5')
insert into tabCustomers (Customer,Branch) values ('AAC','Delhi9')
insert into tabCustomers (Customer,Branch) values ('AAD','Delhi10')
insert into tabCustomers (Customer,Branch) values ('AAD','Delhi11')
insert into tabCustomers (Customer,Branch) values ('ACB','Delhi6')
insert into tabCustomers (Customer,Branch) values ('ACB','Delhi7')
insert into tabCustomers (Customer,Branch) values ('ACB','Delhi8')
--*/

DECLARE @customer varchar(20)
DECLARE merge_cursor CURSOR FAST_FORWARD FOR 
SELECT distinct customer FROM tabCustomers
DECLARE @r VARCHAR(8000) 


OPEN merge_cursor 
declare @temp table
(
Customer varchar(100),
Branch varchar(200)
)

FETCH NEXT FROM merge_cursor INTO @customer 

WHILE @@FETCH_STATUS = 0
BEGIN
set @r=''
SELECT @customer=customer,
@r = ISNULL(@r+',', '') 
        + branch 

        FROM tabCustomers 
        WHERE customer = @customer 

insert into @temp
SELECT @customer,@r 


FETCH NEXT FROM merge_cursor INTO @customer
END

select customer,branch=right(branch,(len(branch)-1))  from @temp

CLOSE merge_cursor 
DEALLOCATE merge_cursor

Thank you.

Regards,
Michelle

ddramireddy showed in his first post how to do it without cursor or loop.
Peso already showed different method in this post
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources