Including columns values excluded by a GROUPBY

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)

Including columns values excluded by a GROUPBY

Author

Topic

hurstgroup
Starting Member

2 Posts

Posted - 2009-07-16 : 12:17:23

I have two rows in a table which I need to return as a single row as they refer to the same item. The problem is that there is a field TYPE which has a different value in each. One will have Text and one HTML. I would like to concatenate these into a new field in the output but I cannot see how to do it!

It maybe that only one of the rows may exist.

This is example source data

SOURCE,TYPE,DESCRIPTION
a111,html,Product1
a111,Text,Product1

I can get the result

a111,Product1

easily but I really want

A111,HTML/Text,Product1

Is this possible?

Thanks

Robert.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-16 : 13:50:54

Try this...

SELECT   STUFF((SELECT '/' + [TYPE] 
                FROM   @t 
                WHERE  SOURCE = t.SOURCE 
                       AND [DESCRIPTION] = t.[DESCRIPTION] 
                FOR XML PATH('')),1,1,'') AS [newcol], 
         t.SOURCE, 
         t.[DESCRIPTION] 
FROM     @t t 
GROUP BY [DESCRIPTION], 
         SOURCE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-16 : 13:51:48

yup with a UDF
create a UDF as


CREATE FUNCTION GetData
(
@Source varchar(5),
@Description varchar(20)
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @RetVal varchar(8000)

SELECT @RetVal=COALESCE(@RetVal+'/','') + TYPE
FROM YourTable
WHERE SOURCE=@Source
AND DESCRIPTION=@Description
RETURN @RetVal
END


then use it as

SELECT DISTINCT SOURCE,dbo.GetData(SOURCE,DESCRIPTION) AS TypeList,DESCRIPTION
FROM YourTable

hurstgroup
Starting Member

2 Posts

Posted - 2009-07-16 : 16:56:01

Hi,

Thanks for the replies. The only problem now is 1) I am using SQL 2000 which I am not convinved supports some of these features and 2) my SQL is too poor to fully grasp what is going on.

This is the exact statement which I would use to get the distinct values - can you please re-write to work with this single statement idea (ideally as I need to do this from an App).

select source, descript_l
from orderresponse
group by source,descript_l

The field which is different is Type and has a value of HTML or Text.

Sorry but I am struggling a bit on this yet it seems so close now I have a solution.

Robert.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-16 : 17:31:11

This modified solution (from visakh) should work in sql server 2000 too:

CREATE FUNCTION GetData
(
@Source varchar(5),
@Description varchar(20)
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @RetVal varchar(8000)

--SELECT @RetVal=COALESCE(@RetVal+'/','') + TYPE
SELECT @RetVal=isnull(@RetVal,'')+case when @RetVal is null then '' else '/' end + TYPE
FROM tab1
WHERE SOURCE=@Source
AND [DESCRIPTION]=@Description
RETURN @RetVal
END

go
create table tab1(
source varchar(255),
[type] varchar(255),
[description] varchar(255)
)
insert tab1
select 'a111','html','Product1' union all
select 'a111','Text','Product1'

--I can get the result
--
--a111,Product1
--
--easily but I really want
--
--A111,HTML/Text,Product1

select * from tab1

SELECT DISTINCT SOURCE,dbo.GetData(SOURCE,[DESCRIPTION]) AS TypeList,[DESCRIPTION]
FROM tab1


drop table tab1
drop function getdata

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-16 : 18:12:12

This is a SQL Server 2008 forum.
Moderator, please move to a SQL Server 2000 forum.

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

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2009-07-16 : 18:13:21

moved.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.5 out!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-16 : 18:15:23

wow - that was fast!

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-16 : 18:16:36

I've said it before. Spirit must be a Microsoft Mole :-)

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

webfred
Master Smack Fu Yak Hacker

8781 Posts