Splitting column for grouping/sorting

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)

Splitting column for grouping/sorting

Author

Topic

R1chard
Starting Member

2 Posts

Posted - 2006-04-23 : 07:38:33

Hi,

I've got table with the data, unfortunately due to the database design theres a bunch of stuff in a single column but I need group but this column for an SQL View....

Example:
Person1 has a value of A,C
Person2 has a value of B,C

I want to produce an SQL view (for reporting) that gives me:

Group A
Person 1

Group B
Person2

Group C
Person1
Person2

So for each 'group' the person is repeated. So in view terms it'll be:

group A, person1
group B, person2
group C, person1
group C, person2

Any thoughts?

Kristen
Test

22859 Posts

Posted - 2006-04-23 : 08:36:28

You could "Split" the delimited column and then report off that, or join it back to the original table if you need more columns

See: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=CSV,Splitting%20delimited%20lists,Best%20split%20functions

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-24 : 02:33:38

Also make sure the table is normalised
http://www.datamodel.org/NormalizationRules.html

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-24 : 05:47:19

Hi all,

R1chard - I don't know if you've now got what you wanted, but here's one approach to your example...

--data
declare @t table (PersonId int, Groups varchar(100))
insert @t
          select 1, 'A,C'
union all select 2, 'B,C'

--calculation
declare @numbers table (i int identity(1, 1), x bit)
insert @numbers select top 100 null from master.dbo.syscolumns a, master.dbo.syscolumns b

select
    left(
        substring(Groups, i, 100),
        charindex(',', substring(Groups, i, 100) + ',')-1) as [Group],
    PersonId
from @t inner join @numbers on substring(',' + Groups, i, 1) = ','

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources