comma delimited processing - 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

General SQL Server Forums

New to SQL Server Programming

comma delimited processing

Author

Topic

sql_beginner
Starting Member

4 Posts

Posted - 2006-12-28 : 17:24:01

Suppose I have a table named Test (referred in the query below)

Category Indicators
ctgy1 Y,,,,
ctgy2 Y,Y,Y,N,
ctgy3 ,Y,,Y,

and If I would like to transform this table to

Category Indicators
ctgy1 Y
ctgy2 Y
ctgy2 Y
ctgy2 Y
ctgy3 Y
ctgy3 Y

I am able to do it using the logic below

CREATE TABLE dbo.Numbers (Number INT IDENTITY(1,1) PRIMARY KEY CLUSTERED)
WHILE COALESCE(SCOPE_IDENTITY(), 0) < 5
BEGIN
INSERT dbo.Numbers DEFAULT VALUES
END

SELECT category,
SUBSTRING( Value, Number, CHARINDEX( ',', Value + ',', Number ) - Number ) as program
FROM Test
inner
JOIN Numbers
ON SUBSTRING( ',' + Value, Number, 1 ) = ','
and CHARINDEX( ',', Value+',', Number ) - Number <> 0
where Number <= Len(Value) + 1

But I would like to Transform this table into something like the one below (where if 'Y' before 1st comma then Q1, if 'Y' Before 2nd comma then Q2 and so on

Category Indicators
ctgy1 Q1
ctgy2 Q1
ctgy2 Q2
ctgy2 Q3
ctgy3 Q2
ctgy3 Q4

What is the best and efficient way to obtain this? Any help will be greatly helpful.

Thanks
Ram

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-29 : 07:39:20

In my opinion,
1. You need to normalize your table structure
2. If first option is not possible, then do this kind of manipulation in the front-end

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-12-29 : 08:37:57

The question is: Are you a) converting data stored as CSV's into a proper table structure, or b) storing CSV's in your data?

If the answer is a), then we can maybe help you out with some ideas. If the answer is b), then you should never store data like that in your tables and always normalize your data. It should be stored in the normalize form in the first place.

- Jeff

sql_beginner
Starting Member

4 Posts

Posted - 2006-12-29 : 09:45:15

JSmith8858: the answer is a.

I totally agree with your suggestions that the data should be normalized.
But the upstream data tables have some columns that is comma delimited and we have no option but to normalize it.
So if there is any way to do this, it will be really great.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-12-29 : 10:26:02

EDIT: new technique ....

This should work for you:


-- here's your sample data:

create table #Data (Category varchar(10), Indicators varchar(20))
insert into #Data
select 'ctgy1','Y,,,,' union all
select 'ctgy2','Y,Y,Y,N,' union all
select 'ctgy3',',Y,,Y,' 
go

-- And here is your final SELECT:

select D.Category, Q.Quarter
from
  (select 'Q1' as Quarter, 'Y,%,%,%,' as Pattern union all
   select 'Q2', '%,Y,%,%,' union all
   select 'Q3', '%,%,Y,%,' union all
   select 'Q4', '%,%,%,Y,') Q
cross join
   #Data D 
where d.indicators like q.pattern


go
drop table #Data

We simply create an "in-line temp table" and cross join your data to it. The temp table contains 1 row per quarter and what pattern the "Indicator" column must match for there to be data for that quarter. Basically, we counting commas on either side of the 'Y' with the patterns.

Then, a simple LIKE clause in the criteria returns only valid quarters for each Category.

The end select is very simple because we are letting the data do all the work, which is the key to writing good SQL -- make sure that your data has as much in there as possible to help you out. The better your data is, the shorter and cleaner your SQL is.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources