Returning one line item for every matching ID

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

General SQL Server Forums

New to SQL Server Programming

Returning one line item for every matching ID

Author

Topic

dzabor
Posting Yak Master

138 Posts

Posted - 2008-01-23 : 09:54:54

I need to use a self join on a table to get it to return one line item ofr all of the line items within the table. The subscriptions table has many line items containing product code. I need to eb able to return one line item containing each of the different product codes.

Table Subscriptions
ID, Product_code
101 ChaptA
101 ChaptB
101 ChaptC

Need to return
ID, Product_codes
101 ChaptA ChaptB ChaptC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-23 : 10:06:33

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

E 12°55'05.25"
N 56°04'39.16"

dzabor
Posting Yak Master

138 Posts

Posted - 2008-01-23 : 10:18:12

That is a lot to follow - I tried a few different formulas, but do nto totally understand them. I am using SQL 2000, please advise.

Thanks,
DZ

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2008-01-23 : 13:09:26

Or try doing this in crystal reports

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

dzabor
Posting Yak Master

138 Posts

Posted - 2008-01-25 : 11:55:22

Thanks!

vijay11singh
Starting Member

1 Post

Posted - 2010-04-24 : 07:56:56

table is
e1
column :
c1 numaric,
c2 varchar

Create FUNCTION [dbo].[GetAliasesById]
(
@userID int
)
RETURNS varchar(2000)
AS
BEGIN
declare @output varchar(2000)
select @output = COALESCE(@output + ', ', '') + c2
from e1
where e1.c1 = @userID

return @output
END

select c1, dbo.GetAliasesByID(c1) as Product_codes from e1 group by c1

Vijay Singh

dzabor
Posting Yak Master

138 Posts

Posted - 2010-07-30 : 18:24:43

I could not get back into the forum to try this - this works great! Thanks!

Celko
Starting Member

23 Posts

Posted - 2010-07-30 : 20:25:31

quote:
Originally posted by dzabor

I need to use a self join on a table to get it to return one line item ofr all of the line items within the table. The subscriptions table has many line items containing product code. I need to eb able to return one line item containing each of the different product codes.

Table Subscriptions
ID, Product_code
101 ChaptA
101 ChaptB
101 ChaptC

Need to return
ID, Product_codes
101 ChaptA ChaptB ChaptC

Author of
SQL for Smarties
Thinking in Sets
SQL Puzzles and Answers
Analytics and OLAP in SQL
SQL Programming Style
Standards in SQL
Data & Databases
Trees & Hierarchies in SQL

dzabor
Posting Yak Master

138 Posts

Posted - 2010-08-02 : 10:19:20

So i created the function, but am having trouble getting the commas to separate the data brought together. I also wonder how to get rid of the NULLs. By definition COALESCE looks for NULLs so i am not sure if that is possible. Below is my function:

create FUNCTION [dbo].[DuesActivityRecordsToOneLineItem]
(
@userID varchar(10)
)
RETURNS varchar(2000)
AS
BEGIN
declare @output varchar(2000)
select @output = COALESCE(@output,'',',') + Left(CONVERT(char(8), thru_date, 112), 4)+ CONVERT(char(20),product_code+'-'+Description, 120)
from Activity
where Activity.ID = @userID
and Activity.Activity_type = 'DUES'

return @output
END

Here are the results (Line four and six have spaces where I would like to place a comma):
ID History
103128 2004-CANDIDATE
41115 NULL
39214 NULL
24986 2010IFAS-IFA-Senior 2010CHAPT/C15-SoCal CA
30301 NULL
25614 2000-CANDIDATE
49911 NULL
107765 2010NEWASSC-New Associat2010CHAPT/C173-Central O

Thanks,
Debi

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-02 : 10:55:45

[code]CREATE FUNCTION dbo.DuesActivityRecordsToOneLineItem
(
@userID varchar(10)
)
RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE @output VARCHAR(2000)

SELECT @output = COALESCE(@output, '', ',')
+ ISNULL(DATENAME(YEAR, thru_date), '')
+ ISNULL(product_code + '-' + Description, '')
FROM Activity
WHERE ID = @userID
AND Activity_type = 'DUES'

RETURN @output
END[/code]

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

dzabor
Posting Yak Master

138 Posts

Posted - 2010-08-02 : 12:16:22

The spaces are gone, but there are no commas either to separate the fields brought together.

output:
ID History
107765 2010NEWASSC-New Associate Member Dues2010CHAPT/C173-Central Oklahoma

Desired output:
output:
ID History
107765 2010NEWASSC-New Associate Member, Dues2010CHAPT/C173-Central Oklahoma

Thanks!
dz

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-02 : 14:08:26

[code]CREATE FUNCTION dbo.DuesActivityRecordsToOneLineItem
(
@userID varchar(10)
)
RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE @output VARCHAR(2000)

SELECT @output = ISNULL(',' + @output, '')
+ ISNULL(DATENAME(YEAR, thru_date), '')
+ ISNULL(product_code + '-' + Description, '')
FROM Activity
WHERE ID = @userID
AND Activity_type = 'DUES'

RETURN @output
END[/code]

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

dzabor
Posting Yak Master

138 Posts

Posted - 2010-08-04 : 12:12:57

I figureed it out!

CREATE FUNCTION dbo.DuesActivityRecordsToOneLineItem
(
@userID varchar(10)
)
RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE @output VARCHAR(2000)

SELECT @output = COALESCE(@output+',', '', ',')
+ ISNULL(DATENAME(YEAR, thru_date), '')
+ ISNULL(product_code + '-' + Description, '')
FROM Activity
WHERE ID = @userID
AND Activity_type = 'DUES'

RETURN @output
END

Thansk for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources