Combining values in one field of crosstab results

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

Site Related Forums

Article Discussion

Combining values in one field of crosstab results

Author

Topic

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2005-05-11 : 16:29:18

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.

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2005-05-12 : 11:17:08

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-12 : 12:39:10

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

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2005-05-12 : 12:45:48

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-12 : 13:56:06

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

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2005-05-12 : 14:15:24

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.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-12 : 14:33:19

[code]
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
[/code]

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-12 : 14:35:12

by the way -- why are you trying do to this in SQL Server? What presentaion layer are you using to output these results?

- Jeff

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2005-05-12 : 14:59:39

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-12 : 15:14:35

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

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2005-05-12 : 15:46:37

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

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2005-05-13 : 13:02:57

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.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-13 : 13:39:45

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

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2005-05-13 : 14:25:18

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-13 : 14:59:00

>>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

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2005-05-13 : 15:05:06

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-13 : 15:19:01

>>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

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2005-05-13 : 15:26:03

Ok, great. Thanks so much for your help!

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2005-05-16 : 16:06:18

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

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2005-05-16 : 16:25:32

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

bleeg
Starting Member

1 Post

Posted - 2005-06-29 : 15:01:45

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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources