| Author |
 Topic  |
|
nicki_assumption
Yak Posting Veteran
69 Posts |
 Posted - 05/11/2005 : 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. |
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
|
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
USA
7423 Posts |
|
|
nicki_assumption
Yak Posting Veteran
69 Posts |
Posted - 05/12/2005 : 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 |
Edited by - nicki_assumption on 05/12/2005 12:56:59 |
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 05/12/2005 : 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 - 05/12/2005 : 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
USA
7423 Posts |
Posted - 05/12/2005 : 14:33:19
|
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 |
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 05/12/2005 : 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 - 05/12/2005 : 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
USA
7423 Posts |
Posted - 05/12/2005 : 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 - 05/12/2005 : 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 - 05/13/2005 : 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
USA
7423 Posts |
Posted - 05/13/2005 : 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 - 05/13/2005 : 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
|
Edited by - nicki_assumption on 05/13/2005 14:59:21 |
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 05/13/2005 : 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 |
Edited by - jsmith8858 on 05/13/2005 14:59:59 |
|
|
|
nicki_assumption
Yak Posting Veteran
69 Posts |
Posted - 05/13/2005 : 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
USA
7423 Posts |
Posted - 05/13/2005 : 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 - 05/13/2005 : 15:26:03
|
| Ok, great. Thanks so much for your help! |
|
|
|
nicki_assumption
Yak Posting Veteran
69 Posts |
Posted - 05/16/2005 : 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 - 05/16/2005 : 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
USA
1 Posts |
Posted - 06/29/2005 : 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 |
|
|
|
Topic |
|
|
|
Combining values in one field of crosstab results
