Multiple records into single record 1

[nuVBer]

I have a table with the following columns: userid, ep
When I run a simple select query I get something like this:

userid ep

us0123 mkt1
us4567 mkt2
us0123 mkt3

What I want is this:

userid ep

us0123 mkt1, mkt3
us4567 mkt2

I can't figure out how to do this. Could someone point me in the right direction?

 

[gmmastros]

Read this: thread183-1159740

Post back if you have any questions.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[nuVBer]

Yes, I read through this but I don't see how that can be used in my situation. I may have several columns that need to be concatenated into the single field being returned but this doesn't seem to address that scenario.

 

[gmmastros]

Can you show us some sample data with expected results?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[nuVBer]

Here is the select and result:

select distinct uid, ep from useraccess order by uid

AT000033 MKT562
AU001214 MKT441
AU001428 R930
AU001507 9987593000
AU001507 Country
AU001507 Market
AU001507 MKT441
AU001507 R109
AU001529 MKT441
CA009436 MKT211
CH066601 MKT544
CH370817 Market
CH370817 MKT100
CH370817 R930
CH370817 R976


I want the result to be:

AT000033 MKT562
AU001214 MKT441
AU001428 R930
AU001507 9987593000, Country, Market, MKT441, R109
AU001529 MKT441
CA009436 MKT211
CH066601 MKT544
CH370817 Market, MKT100, R930, R976


Thanks for any ideas...

 

[gmmastros]

First, create this function

Create Function Getep
  (@uid Integer)
Returns VarChar(8000)
As
Begin
Declare @Result VarChar(8000)

Select @Result = IsNull(@Result + ',', '') + ep
From   UserAccess
Where  ep Is Not NULL
       And uid = @uid
Order By ep

Return @Result
End

Then, run this....

select distinct uid, dbo.Getep(uid)
from useraccess order by uid

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[nuVBer]

Not sure what's going on but here is a partial list of the resulting recordset.

AT000033 NULL
AU001214 NULL
AU001428 NULL
AU001507 NULL
AU001529 NULL
CA009436 NULL
CH066601 NULL
CH370817 NULL
CZ000015 NULL
CZ000022 NULL
DE010275 NULL
DE010298 NULL
DE010549 NULL
DE010833 NULL
ES100946 NULL
FR080292 NULL
HK000006 NULL
JP001043 NULL

All records have NULL as the second column.

 

[nuVBer]

After changing the type on the @Result in the function I got the following result:

AT000033 MKT562,MKT562,MKT562,MKT562,MKT562
AU001529 MKT441,MKT441,MKT441
CA009436 MKT211,MKT211,MKT211
CH066601 MKT544,MKT544,MKT544
CZ000015 MKT412,MKT412,MKT412
CZ000022 MKT552,MKT552,MKT552
JP001043 MKT412,MKT412,MKT412,MKT825,MKT825,MKT825


Which is very close to what I am wanting except that for some reason it is duplicating the values on records where there should be single values. In fact there are exactly 3 values for each single value. The last record should be MKT412, MKT825. I don't understand why it would be doing this. Any idea?

 

[gmmastros]

You said that you changed the function. Can you post the function as it is right now? I'll then be able to show you how to modify it to remove the duplicates.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[nuVBer]

I just changed the type on uid from int to nvarchar on the second line of the function. Nothing else changed. It is now:

Create Function Getep
(@uid nvarchar(50))
Returns VarChar(8000)
As
Begin
Declare @Result VarChar(8000)

Select @Result = IsNull(@Result + ',', '') + ep
From UserAccess
Where ep Is Not NULL
And uid = @uid
Order By ep

Return @Result
End

 

[gmmastros]

This should filter the duplicates. Give it a try.

Create Function Getep
  (@uid nvarchar(50))
Returns VarChar(8000)
As
Begin
Declare @Result VarChar(8000)

Select @Result = IsNull(@Result + ',', '') + ep
From   (
       Select Distinct ep
       From   UserAccess
       Where  ep Is Not NULL
              And uid = @uid
       ) As A
Order By ep

Return @Result
End

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[nuVBer]

That's it!!! Great work George!! I really appreciate you sticking with me on this and solving this problem.