SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Combining values in one field of crosstab results
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 05/11/2005 :  16:29:18  Show Profile  Reply with Quote
Hi- I'm trying to create a table that aggregates a varchar column into comma seperated values within one field. So, I'm using the crosstab procedure from this article, with the size modification
http://weblogs.sqlteam.com/jeffs/
It allows me to get counts of a value in a column, but not concatenate the values.
Example:
So if this were your data:

drop table tmp2
create table tmp2 (PrID int,CName varchar(10), CValue varchar(10))
insert into tmp2
select 1, 'meds','asprin'
union all select 2,'meds','asprin'
union all select 2,'meds','ibuprofen'
union all select 3,'meds','bayer'
union all select 3,'meds','asprin'
union all select 3,'meds','ibuprofen'
union all select 3,'area','arm'
union all select 3,'area','leg'

and you want to get as a result

prid|meds|area
-----------------
1|asprin|NULL|
2|asprin,ibuprofen|NULL|
3|asprin,ibuprofen,bayer|arm,leg|

I was thinking I could use a concat function or something?
I wouldn't mind if those became column heads meds_aspirin like in the example in that article, but I won't know what the values are necessarily to put in the ELSE part of that parameter. Is it possible to use that crosstab procedure for this?
Thank you if any one has an idea.

Sorry to reference another post but it was getting really long.

Edited by - nicki_assumption on 05/12/2005 13:04:38

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 05/12/2005 :  11:17:08  Show Profile  Reply with Quote
Just to add to that info.. this is how i can get the csv value, I just want to figure out how to loop through all the PRIDs and get it into the crosstab procedure. (This still seems like it's in the wrong forum- does anyone have the power to move it for me to some general forum? Sorry!)

CREATE PROCEDURE csv_get AS

DECLARE @List varchar(100)

SELECT @List = COALESCE(@List + ', ', '') +
CAST(CVALUE AS varchar(25))
FROM tmp2
WHERE PRID = 3

SELECT @List
GO
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 05/12/2005 :  12:39:10  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Did you try searching the articles for CSV?

see: http://www.sqlteam.com/item.asp?ItemID=11021

and be sure to read the comments for a really easy User Defined Function idea ....

- Jeff
Go to Top of Page

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 05/12/2005 :  12:45:48  Show Profile  Reply with Quote
Yeah- I read that one. There are a lot of forum submissions and articles here that do this for one list, for which I think the coalesce option is the nicest. But I need it for more than one list, and I won't know how many. This is why I was thinking the crosstab would work for me b/c it allows you to have many of what I'm calling "CNAMES". I think I will need to use perl- I was trying avoid it b/c it's not on the machine :)
Thanks.

coalesce would work like this, if anyone's interested...
(i would need to loop through all prids and cnames, but each would do this- the function would require both cname and prid values)
CREATE PROCEDURE csv_get AS

DECLARE @List varchar(100)

SELECT @List = COALESCE(@List + ', ', '') + CVALUE
FROM tmp2
WHERE PRID = 3
and CNAME='area'

SELECT @List
GO

Edited by - nicki_assumption on 05/12/2005 12:56:59
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 05/12/2005 :  13:56:06  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Are you SURE you read the article and the comments? The comment from Brymol explains exactly what you need to do and it lets you acheive the exact results you are looking for quite easily.

- Jeff
Go to Top of Page

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 05/12/2005 :  14:15:24  Show Profile  Reply with Quote
Yes- I think that comment is in another post though- unless you are referring to what he says about updates in the debate that is linked to that article.
I'm pretty familiar with the Page47 technique though- there are quite a few submissions about it. Still seems easier to me to use coalesce.

I don't think I was clear enough though in my original request- b/c i didn't include more than one column header so maybe you didn't see that i am not looking for just one list. I had originally put prid|meds but I meant to write prid|meds|area for column headers.

I'd love to loop through the prids, and then loop through the CNAMES and use my coalesce function. will be terribly slow but it's a small data set.
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 05/12/2005 :  14:33:19  Show Profile  Visit jsmith8858's Homepage  Reply with Quote

create table tmp2 (PrID int,CName varchar(10), CValue varchar(10))
insert into tmp2
select 1, 'meds','asprin'
union all select 2,'meds','asprin'
union all select 2,'meds','ibuprofen'
union all select 3,'meds','bayer'
union all select 3,'meds','asprin'
union all select 3,'meds','ibuprofen'
union all select 3,'area','arm'
union all select 3,'area','leg'

go


create function List(@prID int, @CName varchar(10))
returns Varchar(8000)
as
begin
	declare @Ret varchar(8000);
	set @Ret = '';
	select @Ret = @Ret + ', ' + CValue 
  	from tmp2 where prID=@prID and CName = @CName
	order by CValue

	return substring(@Ret,3,8000)
end

go

select prID, dbo.List(prID,'meds'), dbo.List(prID,'area')
from
  (select distinct prID from tmp2) IDs

go

drop function List
drop table tmp2




- Jeff

Edited by - jsmith8858 on 05/12/2005 14:36:20
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 05/12/2005 :  14:35:12  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
by the way -- why are you trying do to this in SQL Server? What presentaion layer are you using to output these results?

- Jeff
Go to Top of Page

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 05/12/2005 :  14:59:39  Show Profile  Reply with Quote
The only reason I am doing it in sql server, is that it's all I have available to me- I would do it in a scripting language in a heart beat- perl ideally. I don't know what the presentation layer will be yet- probably excel- just need to dump data. I just need to set up giant cross tab tables so that they can be reported on. But the tables will always be different- your crosstab function hooked me b/c it's exactly what I need if only sql server had an aggregate function for strings like sybase anywhere. I won't always know that "meds" and "area" are going to be the column heads. I have to loop, there's no way of getting around that I can see.
thanks
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 05/12/2005 :  15:14:35  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Don't forget that Excel has cross-tabing and pivoting built in, and it is infinitely quicker and more flexible than trying to hard-code it all in SQL Server.

If you bring the raw data into Excel, it can easily summarize it and pivot it any way each user wants.

- Jeff
Go to Top of Page

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 05/12/2005 :  15:46:37  Show Profile  Reply with Quote
I want to eventually have filtering options on some of those columns- a query tool. And I don't know that everyone will have excel/MS- I know I need to output a CSV file. But excel is great, that's true. THanks
Go to Top of Page

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 05/13/2005 :  13:02:57  Show Profile  Reply with Quote
Jeff- I am trying to use the function above in the crosstab procedure to create the sql that I want with all the column heads.

The only lines I have changed are: (i can paste the whole thing)
@tmp = replace(replace(@Summaries,'(','(CASE WHEN ' + @PivotCol + '=''' + Pivot +
''' THEN dbo.List(PrID,'),')[', ') END) as [' + Pivot ),

This is what I want it to look like:
select PRID,
MAX(CASE WHEN CNAME='area' THEN dbo.List(PrID,'area') END) as[area],
MAX(CASE WHEN CNAME='meds' THEN dbo.List(PRid,'meds') END) as [meds]
from (select PRID, CNAME,CVALUE from tmp2) A
GROUP BY PRID

This is what I get when I print it out which is expected:
select PRID,
MAX(CASE WHEN CNAME='area' THEN dbo.List(PrID,CVALUE) END) as[area],
MAX(CASE WHEN CNAME='meds' THEN dbo.List(PrID,CVALUE) END) as [meds] from (select PRID, CNAME,CVALUE from tmp2) A
GROUP BY PRID

So, I need to get the actual CVALUE value- this is "Pivot" in the code but I'm not sure where I need to put the word Pivot in the code b/c of the replace function.

Don't worry if you are busy- I've asked a lot of questions. Thanks so much for answering them so far.
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 05/13/2005 :  13:39:45  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
That cross-tab procedure wouldn't work for you ... you don't need MAX or CASE or anything ... all you need is something that will dynamically write for you:

select prID, dbo.List(prID,'meds'), dbo.List(prID,'area'), dbo.List(prID, xxxx) ... etc ...
from
<some distinct set of prID's>

where the values 'meds' and 'area' and so on are looped and added to the select listed in the format shown. There is no need for case or summarizing, the function is doing that.



- Jeff
Go to Top of Page

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 05/13/2005 :  14:25:18  Show Profile  Reply with Quote
Right- in perl, i can quickly do a foreach loop. I haven't been able to find anything like that in tsql. I guess this is a good candidate for a cursor. I will try that. Thanks
Like so in really messy code.... (but i'll add in variables instead of hard coding)
CREATE PROCEDURE cross_list (@Select varchar(1000))

AS
set nocount on
set ansi_warnings off

declare @SQL varchar(8000);
declare @CNAME varchar(150);
declare @counter int;
declare @maxid int;
select
@maxid = count(distinct prid)
from tmp2

SET @SQL = ' select PrID, '


DECLARE C_CNAME CURSOR FOR
SELECT DISTINCT CNAME
FROM TMP2


OPEN C_CNAME
FETCH NEXT FROM C_CNAME INTO @CNAME
WHILE @@FETCH_STATUS = 0
BEGIN
set @counter= @counter+1;
set @SQL= @SQL + ' dbo.List(prID,' + @CNAME + ') '

FETCH NEXT FROM C_CNAME INTO @CNAME
IF @counter <= @maxid
BEGIN
SET @SQL= @SQL + ','
END
END

CLOSE C_CNAME
DEALLOCATE C_CNAME
---------------
-- RETURN DATA
---------------
set @SQL=@SQL+ 'from (' + @Select + ') A'
print @SQL
--exec(@SQL)
GO

Edited by - nicki_assumption on 05/13/2005 14:59:21
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 05/13/2005 :  14:59:00  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
>>I haven't been able to find anything like that in tsql

You haven't? Are you SURE you read the article and the comments?

What does this return:

create table tmp2 (PrID int,CName varchar(10), CValue varchar(10))
insert into tmp2
select 1, 'meds','asprin'
union all select 2,'meds','asprin'
union all select 2,'meds','ibuprofen'
union all select 3,'meds','bayer'
union all select 3,'meds','asprin'
union all select 3,'meds','ibuprofen'
union all select 3,'area','arm'
union all select 3,'area','leg'


declare @s varchar(8000);
set @s = ''
select @s = @s + ', dbo.List(prID,''' + CNAME + ''')' from 
  (select distinct cname from tmp2) t
print 'select prID, ' + substring(@s,3,8000) + ' from ....'

go
drop table tmp2


It's the exact same thing .... in fact, you even posted this exact same code ! Take a few minutes, step back, go over what you need to do and the tools you have at your disposal ....

- Jeff

Edited by - jsmith8858 on 05/13/2005 14:59:59
Go to Top of Page

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 05/13/2005 :  15:05:06  Show Profile  Reply with Quote
WHich article are you referring to- sorry. There are a ton about coalesce/list/etc. Mine is so much longer. Is there something innately wrong with cursors though? I put mine in the previous comment as an edit
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 05/13/2005 :  15:19:01  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
>>Is there something innately wrong with cursors though?

Other than the fact that the cursor code is longer, more complicated, and runs slower, nothing.

You've already shown an example of the code I just wrote! you used the same technique in your 2nd post in this thread! I just applied it to the situation you were asking about -- "how do you build that SELECT statement with the function calls?" Well, there it is -- and it uses techniques that you've already read about and then even demonstrated in this very thread.

- Jeff
Go to Top of Page

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 05/13/2005 :  15:26:03  Show Profile  Reply with Quote
Ok, great. Thanks so much for your help!
Go to Top of Page

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 05/16/2005 :  16:06:18  Show Profile  Reply with Quote
Though- can you explain the substring that you are performing in the following...
(I'm finding the cursor too slow so now troubleshooting this..)
substring(@s,3,8000)?
declare @s varchar(8000);
set @s = ''
select @s = @s + ', dbo.List(prID,''' + CNAME + ''')' from
(select distinct cname from tmp2) t
print 'select prID, ' + substring(@s,3,8000) + ' from ....'

go
Go to Top of Page

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 05/16/2005 :  16:25:32  Show Profile  Reply with Quote
This is the exact sp and then when I print out the variable that I am building, it is truncated to 4000. I have changed the function to return a varchar of 100 but that didn't change anything.

CREATE PROCEDURE list_table2 AS
declare @s varchar(8000)
set @s = ''
select @s = @s + ', dbo.List_SIProcs(prID,''' + ItemName + ''')' from
(select distinct ItemName from tblSIProcs2 where ItemName is not null) t
--print Len(@s)
print @s
set @s = 'select prID, ' + substring(@s,3,8000) + ' from (select PRID from tblSIProcs2 where PRID is not null) A
GROUP BY PRID'
print (@s)
GO

Function is
create function List_SIProcs(@prID int, @ItemName varchar(10))
returns Varchar(100)
as
begin
declare @Ret varchar(100);
set @Ret = '';
select @Ret = @Ret + ', ' + ItemData
from tblSIProcs2 where prID=@prID and ItemName = @ItemName
order by ItemData

return substring(@Ret,3,8000)
end

Go to Top of Page

bleeg
Starting Member

USA
1 Posts

Posted - 06/29/2005 :  15:01:45  Show Profile  Reply with Quote
I am trying to do a similar function where I can take results from a table (more than 1 row with a unique index) and concat them into one text or string field. IE:

select substance + ' ' + amount + ' ' + frequency
from FD__SUBSTANCES_ABUSED
where admissionkey= 15679

Results:
Barbiturates 3 pills Daily
Hallucinogens 1 tab Daily

Can I get these 2 rows into one Text or String? IE:
Barbiturates 3 pills Daily and Hallucinogens 1 tab Daily

Thanks...Bleeg

Bleeg
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000