Multiple row values as one 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 2000 Forums

Transact-SQL (2000)

Multiple row values as one column

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-04-19 : 07:31:22

I have a table

TblA
----
ProductName
ABC
DEF
XYZ

I have to fetch these records as one column result with added characters.Like this:

'ABC','DEF','XYZ';

Found a article [url]http://www.sqlteam.com/item.asp?ItemID=2368[/url] and came close to this:

'ABC','DEF','XYZ

Missing part is the last '; at the last. Any ideas?

Karunakaran
___________
NOTHING IS IMPOSSIBLE, IMPOSSIBLE JUST TAKES LONGER...

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-04-19 : 07:52:23

declare @str varchar(4000)

select @str= coalesce(@str + ',' ,'') + coalesce(('''' + productname+ ''''),'') from tblA

print substring(@str,1,len(@str)-1) + ';'

--actually you can remove the last coalesce for productname

--------------------
keeping it simple...

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-04-19 : 09:03:39

It Works.
Thanks Jen.

Karunakaran
___________
NOTHING IS IMPOSSIBLE, IMPOSSIBLE JUST TAKES LONGER...

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-04-19 : 11:50:35

How can use the above query to fetch other columns from the same table also..

TblA
-----
CustomerName ProductName
Karunakaran ABC
Karunakaran DEF
Karunakaran XYZ

I need an output as
CustomerName ProductName
Karunakaran 'ABC', 'DEF', 'XYZ';

Karunakaran
___________
NOTHING IS IMPOSSIBLE, IMPOSSIBLE JUST TAKES LONGER...

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-04-25 : 08:42:25

This works for a explicit value:


Query1:
declare @str varchar(4000)
select @str= COALESCE(@str + '; ', '') + proddescription from TempTable T1 Where ContactId=3
select @str

what I need is, for each distinct contactid returned by my other query I need to run this query.


select distinct contactid from temptable t1

This will give an output like this
ContactId
----------
1
2
3
4
5

For all these five records I want to execute Query1.

Karunakaran
___________
NOTHING IS IMPOSSIBLE, IMPOSSIBLE JUST TAKES LONGER...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-25 : 08:56:01

put the whole query1 into a user defined function and call it in your query.

Go with the flow & have fun! Else fight the flow

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-04-25 : 17:22:04

The UDF way worked.

. Thanks Spirit1.


/****** Object:  Table [dbo].[karunaTempTable]    Script Date: 4/26/2005 2:17:14 AM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[karunaTempTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[karunaTempTable]
GO

/****** Object:  Table [dbo].[karunaTempTable]    Script Date: 4/26/2005 2:17:15 AM ******/
CREATE TABLE [dbo].[karunaTempTable] (
	[ContacName] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ContactId] [int] NOT NULL ,
	[ProductId] [nvarchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ProductDescription] [nvarchar] (600) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[OrderNo] [int] NOT NULL ,
	[OrderDate] [smalldatetime] NOT NULL 
) ON [PRIMARY]
GO

Values are:
ContacName	ContactId	ProductId	ProductDescription	OrderNo	OrderDate
Karunakaran	1	P1001	XYZ	12	2005-04-16 00:00:00
Karunakaran	1	P1002	ABC	12	2005-04-16 00:00:00
Arun	3	P1002	ABC	26	2005-04-15 00:00:00
Arun	3	S1002	ABC	26	2005-04-15 00:00:00
Arun	3	P1001	XYZ	30	2005-04-16 00:00:00

I need to compare the productid starting with 'P' with other products starting with S. I have to fetch these records and I use the followig query which gives me the desired outpt.


select * from karunatemptable t1 inner join 
(select orderdate,orderno,productid,productdescription from karunatemptable) t2 on t1.orderdate = t2.orderdate and 
t1.orderno = t2.orderno and T1.Productid = Replace(t2.productid,'P','S') and t2.productid like 'P%' and t1.productdescription is not Null

Output of this is:
ContacName	ProductId	productdescription
Arun	S1002	ABC

What I need is the same as above but only if the t1.productid doesnt not match with the Replace(t2.productid,'P','S'), So the desired out put is

ContacName ContactId ProductId ProductDescription OrderNo OrderDate
Karunakaran 1 P1001 XYZ 12 2005-04-16 00:00:00
Karunakaran 1 P1002 ABC 12 2005-04-16 00:00:00
Arun 3 P1001 XYZ 30 2005-04-16 00:00:00

[/code]

Any Ideas?

Karunakaran
___________
It's better to be loved and lost, than ever to be loved...

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-04-25 : 19:11:50

Finally I got it working...


Query #1 Using Joins
--------------------
select T3.contactname,T3.productid,T3.productdescription,T3.orderno from karunatemptable T3 Left Outer Join 
karunatemptable T1 inner join karunatemptable T2 on T1.orderdate=T2.orderdate
and T1.orderno=T2.orderno and T1.contactid=T2.contactid and T2.Productid = Replace(T1.productid,'P','S')
ON T3.contactid=T1.contactid and T3.orderno=T1.orderno and T3.orderdate=T1.orderdate where T1.orderno is null

Query #2 Using Not Exists
-------------------------
select contactname,productid,productdescription,orderno from karunatemptable T3 where not exists (
select T1.contactname,T1.productid,T1.productdescription from karunatemptable T1 inner join karunatemptable T2 on 
T1.orderdate=T2.orderdate 
and T1.orderno=T2.orderno and T1.contactid=T2.contactid and
T2.Productid = Replace(T1.productid,'P','S')
where T3.contactid=T1.contactid and T3.orderno=T1.orderno and
T3.orderdate=T1.orderdate)

Output as expected..
contactname	productid	productdescription	orderno
Karunakaran	P1001	        XYZ	                12
Karunakaran	P1002	        ABC	                12
Arun	        P1001	        XYZ	                30

Okay..Now which one of the above query is better?

According to execution plan, the joins involves more processes than the not exists query, So, Should I stick with Not Exists?

Karunakaran
___________
It's better to be loved and lost, than ever to be loved

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources