Select-->Convert multiple rows into one row

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)

Select-->Convert multiple rows into one row

Author

Topic

ramzansadiq
Starting Member

6 Posts

Posted - 2009-02-23 : 23:15:29

Hello to everybody,

I have spent a lot of time but haven't found a way yet.I have 5 tables as follows
Comm-Master (Comm_ID, Comm_Route,Comm_Date, etc)-->C
Comm-Details (Comm_ID, Subject_ID, Employer_ID, Player_ID)-->CD
Subject(ID,Sub_Name)-->S
Employer(ID,E_Name)-->E
Player(ID,Name)

I have data as follows

Comm_ID Comm_Date Sub_Name E_Name
101 1/1/2009 Science IBM
101 1/1/2009 Math -
102 2/1/2009 C# Microsoft
102 2/1/2009 VB.Net TeamSuper
103 2/1/2009 VB6 GE

I want data as follows ( display sub_name and E_name with ',' in one row if more than 1)

Comm_ID Comm_Date Sub_Name E_Name
101 1/1/2009 Science,Math IBM
102 2/1/2009 C#,VB.Net Microsoft,TeamSuper
103 2/1/2009 VB6 GE

My query which works fine for the 1st output,but I am unable to get the 2nd even after getting some hair into grey.

SELECT S.Sub_Name, E.Name, F.Comm_ID, F.Comm_Date
FROM dbo.Comm_Details CD
LefT OUTER JOIN dbo.Employer E ON CD.Employer_ID = E.Employer_ID
LEFT OUTER JOIN dbo.Subject S ON CD.Subject_ID = S.Subject_ID
LEFT OUTER JOIN dbo.Comm_Master C ON CD.Comm_ID = C.Comm_ID

I am using sql server 2000, Is it possible using UDF or with queries or Do i have to use Cursors, if yes could you guide me a bit.

you help is greatly appreciated

RS

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-23 : 23:23:38

Try this one,

declare @temp table (Comm_ID int, Comm_Date varchar(32), Sub_Name varchar(32),E_Name varchar(32))
insert into @temp
select 101, '1/1/2009',' Science', 'IBM' union all
select 101, '1/1/2009', 'Math' ,NULL union all
select 102, '2/1/2009', 'C#' ,'Microsoft' union all
select 102, '2/1/2009', 'VB.Net','TeamSuper' union all
select 103, '2/1/2009', 'VB6','GE'

select distinct Comm_ID,Comm_Date,
stuff((select distinct ','+Sub_Name from @temp where comm_id = t.comm_id for xml path('')),1,1,'') as sub_name ,
stuff((select distinct ','+ E_Name from @temp where comm_id = t.comm_id for xml path('')),1,1,'') as E_Name from @temp

raky
Aged Yak Warrior

767 Posts

Posted - 2009-02-23 : 23:30:45

quote:
Originally posted by Nageswar9

Try this one,

declare @temp table (Comm_ID int, Comm_Date varchar(32), Sub_Name varchar(32),E_Name varchar(32))
insert into @temp
select 101, '1/1/2009',' Science', 'IBM' union all
select 101, '1/1/2009', 'Math' ,NULL union all
select 102, '2/1/2009', 'C#' ,'Microsoft' union all
select 102, '2/1/2009', 'VB.Net','TeamSuper' union all
select 103, '2/1/2009', 'VB6','GE'

select distinct Comm_ID,Comm_Date,
stuff((select distinct ','+Sub_Name from @temp where comm_id = t.comm_id for xml path('')),1,1,'') as sub_name ,
stuff((select distinct ','+ E_Name from @temp where comm_id = t.comm_id for xml path('')),1,1,'') as E_Name from @temp

I am using sql server 2000, Is it possible using UDF or with queries or Do i have to use Cursors, if yes could you guide me a bit.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-23 : 23:46:25

[code]
try this
CREATE FUNCTION dbo.concat_tab_products (
@Comm_ID int
)
RETURNS varchar(1000)
AS
BEGIN
DECLARE @return varchar(1000)

SELECT @return =Coalesce(@return + ',', '') + sub_name
FROM dbo.urtablename
WHERE Comm_ID = @Comm_ID

RETURN @return
END

SELECT distinct comm_id,comm_date
, dbo.concat_tab_products(Comm_ID) as Sub_Name
FROM dbo.urtablename
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-24 : 10:44:14

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-24 : 10:54:38

quote:

Originally posted by bklr


try this
CREATE FUNCTION dbo.concat_tab_products (
   @Comm_ID int
)
  RETURNS varchar(1000)
AS
  BEGIN
    DECLARE @return varchar(1000)

    SELECT @return =Coalesce(@return + ',', '') + sub_name+ E_Name
    FROM   dbo.urtablename
    WHERE  Comm_ID = @Comm_ID

    RETURN @return
  END

SELECT distinct comm_id,comm_date
     , dbo.concat_tab_products(Comm_ID) as Sub_Name
FROM  dbo.urtablename

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-24 : 12:44:09

quote:

Originally posted by sodeep

quote:

Originally posted by bklr


try this
CREATE FUNCTION dbo.concat_tab_products (
   @Comm_ID int
)
  RETURNS varchar(1000)
AS
  BEGIN
    DECLARE @return varchar(1000)

    SELECT @return =Coalesce(@return + ',', '') + sub_name+ E_Name
    FROM   dbo.urtablename
    WHERE  Comm_ID = @Comm_ID

    RETURN @return
  END

SELECT distinct comm_id,comm_date
     , dbo.concat_tab_products(Comm_ID) as Sub_Name
FROM  dbo.urtablename

this will give both field values in same column. i thinl op wants all both field values as seperate csvs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-24 : 13:00:50

you might need two udfs one for each value


CREATE FUNCTION dbo.concat_sub_names (
   @Comm_ID int
)
  RETURNS varchar(1000)
AS
  BEGIN
    DECLARE @return varchar(1000)

    SELECT @return =Coalesce(@return + ',', '') + sub_name
    FROM   dbo.urtablename
    WHERE  Comm_ID = @Comm_ID

    RETURN @return
  END

and


CREATE FUNCTION dbo.concat_e_names (
   @Comm_ID int
)
  RETURNS varchar(1000)
AS
  BEGIN
    DECLARE @return varchar(1000)

    SELECT @return =Coalesce(@return + ',', '') +  E_Name
    FROM   dbo.urtablename
    WHERE  Comm_ID = @Comm_ID

    RETURN @return
  END

then use it like


SELECT distinct comm_id,comm_date
     , dbo.concat_sub_names (Comm_ID) as Sub_Name,
dbo.concat_e_names(Comm_ID) AS e_name
FROM  dbo.urtablename

ramzansadiq
Starting Member

6 Posts

Posted - 2009-02-24 : 15:31:22

Thanks you so much for every1 reply.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-24 : 23:20:22

Welcome

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-25 : 03:28:04

quote:

Originally posted by sodeep

quote:

Originally posted by bklr


try this
CREATE FUNCTION dbo.concat_tab_products (
   @Comm_ID int
)
  RETURNS varchar(1000)
AS
  BEGIN
    DECLARE @return varchar(1000)

    SELECT @return =Coalesce(@return + ',', '') + sub_name+ E_Name
    FROM   dbo.urtablename
    WHERE  Comm_ID = @Comm_ID

    RETURN @return
  END

SELECT distinct comm_id,comm_date
     , dbo.concat_tab_products(Comm_ID) as Sub_Name
FROM  dbo.urtablename

i will not give the required output
as visakh suggested u should use two udf functions

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources