separate and combine CSV list - SQL Server Forums

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

Transact-SQL (2000)

separate and combine CSV list

Author

Topic

lauramayer
Posting Yak Master

152 Posts

Posted - 2006-04-13 : 16:35:11

I'm really not sure how to ask this so here goes:

I have a field CallReason that can have many values like so:

Login,Instructions,Software

it can have a litle as one. What I need to do is separate that out so it counts each value like this:


select 
Software = SUM(CASE WHEN CallReason like '%Software%' THEN 1 ELSE 0 END), 
Hardware = SUM(CASE WHEN CallReason like '%Hardware%' THEN 1 ELSE 0 END), 
Email = SUM(CASE WHEN CallReason like '%Email%' THEN 1 ELSE 0 END), 
Instructions = SUM(CASE WHEN CallReason like '%Instructions%'  THEN 1 ELSE 0 END),
MissingReport = SUM(CASE WHEN CallReason like '%MissingReport%' THEN 1 ELSE 0 END), 
SoftwareUpgrade = SUM(CASE WHEN CallReason like '%SoftwareUpgrade%'  THEN 1 ELSE 0 END),
Login = SUM(CASE WHEN CallReason like '%Login%'  THEN 1 ELSE 0 END),
Other = SUM(CASE WHEN CallReason like '%Other%'  THEN 1 ELSE 0 END)
from dbo.tblISSatisfaction

I get this result:


Software    Hardware    Email      
----------- ----------- ----------- 
2           0           1

Which is good but now I need to take that and make it two columns like so:


CallReason Count
Software     2
Hardware     0
Email        1

If someone could point me in the right direction I would greatly appreciate it.

Thanks so much

Laura

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-13 : 19:10:44

[code]select CallReason, count(*)
from tblISSatisfaction
group by CallReason[/code]

lauramayer
Posting Yak Master

152 Posts

Posted - 2006-04-14 : 07:22:15

Thanks for that, but I need to have them separated. This just gets me the distinct groupings. Instead of


CallReason       Count
Login            2
Instructions     2
Software         2
Email            1

I get:


CallReason                                                     
-------------------------------------------------- ----------- 
Email                                              1
Login,Instructions,Software                        2

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-14 : 07:24:33

Refer this
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

Madhivanan

Failing to plan is Planning to fail

lauramayer
Posting Yak Master

152 Posts

Posted - 2006-04-14 : 07:56:53

I guess I really am confused. There already concatenated in the field. I need to break them apart then count them.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-14 : 08:01:07

Sorry, missed out that point. You can use the CSVTable here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830 to split them up first

lauramayer
Posting Yak Master

152 Posts

Posted - 2006-04-14 : 09:20:16

Thanks to you both,but I dont understand either solution.

Thanks anyway.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-14 : 09:58:38

Can you post your table structre, some sample data and the expected result

lauramayer
Posting Yak Master

152 Posts

Posted - 2006-04-14 : 10:44:50

Thanks for that:


CREATE TABLE [dbo].[tblISSatisfaction] (
	[CustSatID] [int] IDENTITY (1, 1) NOT NULL ,
	[CallReason] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
GO

INSERT INTO tblISSatisfaction 
(CallReason) 
VALUES ('Hardware,Software')

INSERT INTO tblISSatisfaction 
(CallReason) 
VALUES ('Hardware,Software')

INSERT INTO tblISSatisfaction 
(CallReason) 
VALUES ('email')

I'd like it to end up like this:


CallReason Count
Software     2
Hardware     2
Email        1

And it might not even be possible which could explain my complete confusion.

Thanks again

Laura

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-14 : 11:50:59

[code]
create function CSVStr(@str varchar(8000), @word_no int)
returns varchar(100)
as
begin
declare @word varchar(100),
@s int,
@c int,
@i int

select @i = 1
select @s = 1
select @c = -1
while (@i <= @word_no and @c <> 0)
begin
select @s = @c + 1
select @c = charindex(',', @str + ',', @s)
select @i = @i + 1
end
select @word = substring(@str, @s, @c - @s)
where @c <> 0
return @word
end
go

select dbo.CSVStr(CallReason, NUMBER), count(*)
from tblISSatisfaction, dbo.F_TABLE_NUMBER_RANGE(1, 10)
where dbo.CSVStr(CallReason, NUMBER) is not null
group by dbo.CSVStr(CallReason, NUMBER)
[/code]
the F_TABLE_NUMBER_RANGE can be obtain from here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685

lauramayer
Posting Yak Master

152 Posts

Posted - 2006-04-14 : 12:08:49

Wow, I cant thank you enough! Thast is just it. I thought it was a simple fix, the complexity behind making something I thought was simple is astounding.

Thank you for taking the time to help.

Laura

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-17 : 03:09:15

If you store csv in table, you need to read this
http://www.datamodel.org/NormalizationRules.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources